Calculate Average Over All Dimension Values in Pivot Table

[mpressey]

In the attached example, how can I calculate the average over all weekdays versus the average over just days with sums?

[sunny_talwar]

What is your expected output for the two numbers and which object do you need this in?

[Anil_Babu_Samineni]

Is this you want to see?

avg(aggr(sum(Visit),Hour,WeekDay,Location))

[mpressey]

In the avg pivot, I would like the average calculation to treat the nulls, '-', as zero values.

[sunny_talwar · Accepted solution]

Try this

If(Dimensionality() = 0,

Sum(Aggr(Avg(Aggr(Sum(Visit),Hour,WeekDay,Location))/Avg(Aggr(Count(DISTINCT Visit),Hour,WeekDay,Location)),Hour,WeekDay,Location))/Count(DISTINCT TOTAL WeekDay),

Avg(Aggr(Sum(Visit),Hour,WeekDay,Location))/Avg(Aggr(Count(DISTINCT Visit),Hour,WeekDay,Location)))

[sunny_talwar]

Simplified version of the expression

If(Dimensionality() = 0,

Sum(Aggr(Avg(Visit),Hour,WeekDay,Location))/Count(DISTINCT TOTAL WeekDay),

Avg(Visit))

[mpressey]

Thank you Sunny. This is very helpful. I have not used dimensionality very much.