topic Help with aggr in pivot table - need to get the overall average by year in App Development

Help with aggr in pivot table - need to get the overall average by year

mikegrattan — Mon, 25 Sep 2023 15:27:44 GMT

I have the following expression in a pivot table measure:

aggr( count(distinct WorkDate),WorkMonth,WorkYear,BusUnit)

It works at the WorkMonth level but I would like to get the average as a total for the year. Please advise...thank you.

Re: Help with aggr in pivot table - need to get the overall average by year

vincent_ardiet_ — Tue, 26 Sep 2023 13:39:41 GMT

You can use dimensionality() function to change the behaviour of your expression based on the level of aggregation you are.
For example:
If(Dimensionality()=2
,aggr( count(distinct WorkDate),WorkMonth,WorkYear,BusUnit)
,aggr(avg(aggr( count(distinct WorkDate),WorkMonth,WorkYear,BusUnit)),WorkYear,BusUnit))

Re: Help with aggr in pivot table - need to get the overall average by year

mikegrattan — Wed, 27 Sep 2023 14:05:34 GMT

Thanks Vincent; I believe this will work.