Problem summing an aggregated average in pivot table

[mabryanjr]

I have a pivot table that calculates average weekly values at a locations within various regions, and I need those average values to sum up to their total within the region.

The expression used to generate the weekly average per location is:

Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location))

vR5Wk is a variable to capture the Event Dates in the most recent 5 weeks

the events are tied to region and location by association to another table via their key

I have tried to sum the aggregation of the above measure, but am not getting the desired results.  Any assistance or thoughts are much appreciated.  Thanks!

[mabryanjr]

To clarify...as you can see in the QS Pivot Table Extract, the totals are an average of the locations in each region.  I need to get the sum.

[Kushal_Chawda · Accepted solution]

@mabryanjr  try below

=if(dimensionality()=1,
sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)),

Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)))

or bit optimized

=if(dimensionality()=1,
Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),

Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)))

[mabryanjr]

Thanks, that is what I needed!