Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an Avg expression in a straight table and pivot table: Avg(days). In the straight table I have the ability to select what type of expression totals I would like. So I select Expression totals: Avg. This gives me the Avg of the Avg for my total.
I really need to use a Pivot table, so I am trying to dupilcate the numbers from above. So, I tried avg(aggr(AVG(Days), PROGRAM, Division, REGION)) . This gives me a match to the above straight table for the totals but I'm now not getting a true average for my dimensions. I'm seeing an average of the averages, not an overall true avg like the straigh table. I hope this is clear. If so, is there a solution?
What I want to see.
Use a Pivot table. Receive a true average of my dimension and for the totals see an Average of an Average, like the straight table.
Could you post which dimensions you are using in your chart and what you want to see at each dimensional level (e.g. using a screenshot of your two charts)?
Maybe try to use Dimensionality() to branch into different expressions:
=If(Dimensionality() = 0, avg(aggr(AVG(Days), PROGRAM, Division, REGION)), Avg(Days) )
I think it would be good to explore a without Dimensionality() solution first before going into that territory. So as Stefan mentioned, it would be good to know what all are your straight table dimensions?
Were you use PROGRAM, Division, REGION as dimensions and Avg(Days) as your expression? or are there more or less dimensions in the straight table? Also do you have the same dimensions in your pivot table? If possible provide a sample to further explore. If nothing works, then I think Dimensionality() should work as Stefan pointed out above
See attached for example. I'm trying to get my pivot table to match the straight table.
I have provided an example below. Thanks in advance!
Here is the dirty way:
If(Dimensionality() = 0, Avg(Aggr(AVG(Days), Reason)), Avg(Days))
Update:
May be Stefan can offer a more cleaner solution. I am unable to make this to work for right now