Forum Discussion
Running Total on a Measure Not Working
Hi,
I am looking to calculate the running total per month from the below measure which gives the 'phased' value per month for the previous year (basically total number of complaints per yer divide by 12).
PFY Phased Complaints =
var total_complaints =
CALCULATE(
[Count Non Conformance],
FILTER(
ALL('Date'),
'Date'[Financial Year2] = "FY " & FORMAT(VALUE(RIGHT(MAX('Date'[Financial Year2]), 2)) - 1, "00") &&
'Date'[Date] <= MAXX(
FILTER(
ALL('Date'),
'Date'[Financial Year2] = "FY " & FORMAT(VALUE(RIGHT(MAX('Date'[Financial Year2]), 2)) - 1, "00")
), 'Date'[Date]
)
),
Query1[NON_CONFORMANCE_DESCRIPTION] = "X"
)
var phased_complaints = total_complaints/12
RETURN
phased_complaints
Looking for the desired outcome of new measure:
| Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec | Jan | Feb | Mar | |
| PFY Phased Complaints | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 | 2.8 |
| New Measure | 2.8 | 5.6 | 8.4 | 11.2 | 14 | 16.8 | 19.6 | 22.4 | 25.2 | 28 | 30.8 | 33.6 |
1 Reply
yaman123 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.
