Struggling to calculate a percenatge value

Question

Struggling to calculate a percenatge value

2Tall

SSChampion

Points: 14644
More actions
June 15, 2010 at 9:57 am

#225616

Hi. I am trying to display a value as a percentage.
In the formula below the values calculated manually are as follows:
SUM of ActualCapacityMinutes = 1010
SUM of ActivityCentreCapacity = 3465
=IIF(Sum(ROUND(Fields!ActualCapacityMinutes.value/Fields!ActivityCentreCapacity.Value,0))*100 >= 100,100,SUM(ROUND(Fields!ActualCapacityMinutes.value/Fields!ActivityCentreCapacity.Value,0))*100) & "%"
I expect a value of 29.14 (1010/3465)*100. The code above displays 100.
Basically if the value exceeds 100 display 100, else show me the value.
Hope that makes sense,
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

2Tall (6/15/2010)
Hi. I am trying to display a value as a percentage.
In the formula below the values calculated manually are as follows:
SUM of ActualCapacityMinutes = 1010
SUM of ActivityCentreCapacity = 3465
=IIF(Sum(ROUND(Fields!ActualCapacityMinutes.value/Fields!ActivityCentreCapacity.Value,0))*100 >= 100,100,SUM(ROUND(Fields!ActualCapacityMinutes.value/Fields!ActivityCentreCapacity.Value,0))*100) & "%"
I expect a value of 29.14 (1010/3465)*100. The code above displays 100.
Basically if the value exceeds 100 display 100, else show me the value.
Hope that makes sense,
Regards,
Phil.

... Struggling to see how it is applicable to T-SQL forum 😀

Is it VB, Excel or something else?

Can you tell what is the range of data in ActualCapacityMinutes and ActivityCentreCapacity? As i can see it does ActualCapacityMinutes/ActivityCentreCapacity, then rounds the result then sums it up, so provided sums of ActualCapacityMinutes and ActivityCentreCapacity are irrelevant here.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

You may be having the problem because it's doing purely integer division 1010/4000 = 0 in int division (decimals ignored).

You might try making the "100" be "100.0" or explicitly forcing the datatype(s) of the values in the divide to be decimal and not integer.

Scott Pletcher, SQL Server MVP 2008-2010

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks Scott. Regards the VB yes this part has been done in visual studio (main code T-SQL in the dataset) so fair point.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper