Forum Discussion
Cumulative daily average in selected range
I am trying to figure out how to present cumulative average per day for a measure, based on the dates selected in a slicer. However, none of the examples I manage to Google quite fits.
So I want to change this, to not be what the average is per day, but what the cumulative average is:
Current result (per individual day):
Current Measure:
Expected result:
(actual cumulative percentage)
I tried adapting this https://blog.enterprisedna.co/calculating-a-rolling-average-in-power-bi-using-dax/ although I could not get the right result. I tried for example:
Rolling average Process score =
AVERAGEX(
FILTER(ALLSELECTED(fact_scores[Handle Date]),
fact_scores[Handle Date] <= MAX(fact_scores[Handle Date])),
[Process Score])
I guess it has to do with "fact_scores[Handle Date]" not specifying the relationship to use, mixed with the measure that already does? I tried adding the "USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])" part to different parts of the rolling average formula, although it would not be accepted anywhere.
Is there any way to do this in DAX, or am I forced to create a new table first that has a cumulative score, cumulative possible score and split score by possible score in a third column, then simply just display it into a visual? My original data table is quite large, so I rather not add calculated columns directly in the table, row by row.
Nice! Thanks. It almost worked, although close enough. I had to switch ALL to ALLSELECTED, like so:
From:
Process Score = CALCULATE( CALCULATE(
AVERAGE(fact__scores[Process score]),
USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])
), Filter(all(dim_Calendar) , dim_Calendar[date] <= max(dim_Calendar[date]) ) )
To:
Process Score = CALCULATE( CALCULATE(
AVERAGE(fact__scores[Process score]),
USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])
), Filter( ALLSELECTED(dim_Calendar) , dim_Calendar[date] <= max(dim_Calendar[date]) ) )
End result:
If you want to update the answer, I will mark it as the solution.
2 Replies
- amitchandak
Super User
Luggruff , try like
Process Score = CALCULATE( CALCULATE(
AVERAGE(fact__scores[Process score]),
USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])
), Filter( all(dim_Calendar) , dim_Calendar[date] <= max(dim_Calendar[date]) ) )Nice! Thanks. It almost worked, although close enough. I had to switch ALL to ALLSELECTED, like so:
From:
Process Score = CALCULATE( CALCULATE(
AVERAGE(fact__scores[Process score]),
USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])
), Filter(all(dim_Calendar) , dim_Calendar[date] <= max(dim_Calendar[date]) ) )
To:
Process Score = CALCULATE( CALCULATE(
AVERAGE(fact__scores[Process score]),
USERELATIONSHIP(dim_Calendar[date],fact_scores[Handle Date])
), Filter( ALLSELECTED(dim_Calendar) , dim_Calendar[date] <= max(dim_Calendar[date]) ) )
End result:
If you want to update the answer, I will mark it as the solution.
