Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an Avg expression in both of my charts, a Pivot and Straight table. I'm having issues with the totals. I would like the total of the Pivot table to match the (correct total Avg) of the straight table. From the research I've done, it seems that the Pivot table does not have the option to create totals, just partial totals. Is there a way that I can adjust my expression to get a final total that matches each table?
=avg(Avg_Person_days)
Hi MrJohn,
The best thing to do in your pivot table is to get an expression that sums to the total and divide by one that gives the count. The trick would be the use of the TOTAL qualifier in the second expression.
Use TOTAL and where necessary use a list of field names too that will tell the expression which dimensions to respect, otherwise TOTAL will disregard all the chart dimensions.
You'll have something like
Sum( ....) / Count(TOTAL <field1, field2,...> ...)
If you post some data I or someone else will make it clear.
Cheers
Andrew
Thanks, yes an example would be great. See below for a list of dimensions.
Pivot Dimensions:
Region
Division
Program
Expression: Avg(Avg_Days)
Something like this!
Sum(Avg_FirstVisit_Days)/Count(TOTAL <Division, REGION, PROGRAM>Avg_FirstVisit_Days)
Hi,
Avg_Days seems an odd name for a field -especially one you trying to find an average of.
We could use an expression like
Sum(Avg_Days) / Count(something)
I don't know what a good denominator would be without knowing more about your data.
For example if I wanted to know the average size of some orders I would use an expression like
Sum([Order Value]) / Count(OrderID)
OrderID would not be a dimension in my chart. For your example I need to know what you mean by average.
Sum of what / count of what.
Cheers
Andrew
Thanks for the feedback. I want to make sure I'm explaining myself correctly, see attached. My pivot table and straight table averages match each other, it's the overall totals that differ. Why is that?