Reportin services - AVG of avg
-
Hello,
I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I must sum average of average. When I sum row I get 93.33, but correctly score is 97,50%.
Maybe someone knows how to do it. How to sum calculated
-
Hi,
I think 93.33% maybe the right answer because the average is being calculated in the scope of the whole year or whatever months (Jan-June) included.
While your expectation of the answer being 97.5 % is wrong because, its just the average of percentages of every month.
Just try to take the average of sales for each individual months (For Ex. sales/30) and then for whole year(sales/365) with some random values.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI -
But how to calculate the resulting field RS. As in the example above, after the calculation as a result I get indirect outcome which I want to sum them up.
Is this possible?
-
wait, I don't understand. You want to sum the up percentages? If you are summing up the percentages, then it would be 487.5%. Or you are looking for (100%+100%+100%+100+87.5%)/5 = 97.5%?
------------
🙂 -
Yes, I'm looking for answers to this (100%+100%+100%+100+87.5%)/5 = 97.5%?
-
You can use the following expression to get the sum of percentages:
=REPORTITEMS!Textbox15.Value
where 'Textbox15' is the textbox in which you are showing the percentage.
you have to get the value of '5' from somewhere...
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI -
The simple school maths level answer
Never average averages.
The slightly better answer
Never average averages unless the sample sizes of all elements are identical
The reason
suppose you have a set of average speeds for traffic along a road
January 30mph
February 45mph
March 15 mph
What is the average speed for the quarter
You cannot tell as you do not have sufficient information - you might think its (30+45+15)/3 = 30
That is highly unlikely to be correct - for two reasons
1 you don't know the sample sizes
2 speeds are ratios
Now suppose you know that you have
January
Vehicle a 10 miles at 30 mph
Vehicle b 5 miles at 30 mph
vehicle c 25 miles at 30 mph
February
Vehicle d 10 miles at 50 mph
Vehicle e 10 miles at 40 mph
March
vehicle f 25 miles at 15 mph
Total distance travelled = 85 miles
Total time taken (10/30)+(5/30)+(25/30)+(10/50)+(10/40)+(25/15)=
3.45 hours
Average speed= 85/3.45
=~ 24.64 mph
-
crmitchell, I agree with you but my supervisor won't avg avg
SSC-Enthusiastic, Your solution"=REPORTITEMS!Textbox15.Value", gives last calculated value, but I want sum in this table.
When I "=sum(REPORTITEMS!Textbox15.Value) I get error messages.
-
You may need to convert the values into numbers before adding them up.
=SUM(CAST(ReportItems!Textbox15.Value AS INT) )
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI -
Hi,
When I used =SUM(CAST(ReportItems!Textbox13.Value AS INT) ) I get the message "The Value expression for the textrun 'sum.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers".
-
You can't Sum ReportItems!, You can reference them for other formulas.
You need to sum the underlying data. but I'm not sure if that helps you in your scenario.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply