Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |