topic Impossible? DAX for Average of A Running Sum (measure) in a Matrix table in DAX Commands and Tips

Impossible? DAX for Average of A Running Sum (measure) in a Matrix table

emilhizer — Thu, 10 Oct 2024 22:01:36 GMT

I'm trying to create a measure for a matrix table that is the average of a running sum.

I have a running sum calculated in a Matrix table, but I seem unable to create a measure to provide the average of this running sum, in the the Matrix table, which can summarize (provide the average) by day, month, year.

This is not an issue using static data, as the DAX expression AVERAGE works as expected. The issue arrizes when I'm trying to average a the outcome of a measure (and specifically a measure that is a running sum / total).

To illustrate, I'll start with static data, which provides the desired outcome, followed by another example but replacing the running sum (static) column a measure that calculates the running sum. I've provided the incorrect solution below as well. It's this second example that I need help with.

Using static data for Gross Adds, Terminations, Net Adds and Ending Customers, I can create the following Matrix (note that Ending Customers is a running sum, by date, of Net Adds, but I've made it static data in this first example below)

AccountInfo2

Matrix2 - This is the correct, desired outcome: Average Customers is the true average of EndingCustomers

KPI2-AverageCustomers = CALCULATE(AVERAGE(AccountInfo2[Ending Customers]), USERELATIONSHIP('Calendar'[Date], AccountInfo2[Date]))

Using dynamic data only

AccountInfo

Ending Customers is derived from a running / cumulative total of Net Adds (this is correct)

NEED HELP: Average Customers is the average of Ending Customers - but notice how my DAX shown below does not calculate as I thought it would (see comparison to Average Customers DAX above, based on static Ending Customers data)

DAX

KPI-GrossAdds = CALCULATE(SUM(AccountInfo[Gross Adds]), USERELATIONSHIP('Calendar'[Date], AccountInfo[Date]))
KPI-Terminations = CALCULATE(SUM(AccountInfo[Terminations]), USERELATIONSHIP('Calendar'[Date], AccountInfo[Date]))
KPI-NetAdds = [KPI-GrossAdds] - [KPI-Terminations]
KPI-AverageCustomers =
VAR CumulativeSum =
CALCULATE([KPI-NetAdds],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
VAR CumulativeSum2 =
IF(ISBLANK([KPI-NetAdds]), BLANK(), CumulativeSum)
VAR TempTable =
SUMMARIZE('Calendar','Calendar'[Date],"EndingCustomers", CumulativeSum2)
RETURN AVERAGEX(TempTable, [EndingCustomers])

KPI-AverageCustomers =
VAR CumulativeSum =
CALCULATE([KPI-NetAdds],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
VAR CumulativeSum2 =
IF(ISBLANK([KPI-NetAdds]), BLANK(), CumulativeSum)
VAR TempTable =
SUMMARIZE('Calendar','Calendar'[Date],"EndingCustomers", CumulativeSum2)
RETURN AVERAGEX(TempTable, [EndingCustomers])

Re: Impossible? DAX for Average of A Running Sum (measure) in a Matrix table

Greg_Deckler — Tue, 08 Oct 2024 21:06:03 GMT

emilhizer This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Impossible? DAX for Average of A Running Sum (measure) in a Matrix table

emilhizer — Wed, 09 Oct 2024 16:47:08 GMT

Greg, thanks for the reply - I followed the instructions / pattern noted in your reply and in your linked article, however, I'm not getting the average over the period (day, month, year) in the matrix, rather my average is only showing the last value (customer) in the period. Nothing seems to be averaging.

Thoughts - remember, I'm trying to take the average of a cumulative sum measure. The cumulative sum (ending customers) below is working fine, but as you can see, the Average Customers measure isn't the average of the Ending Customers for each period (day, month, year). See my original information above for what the expected average is.