cancel
Showing results for
Did you mean:  Helper II

## Running Total On A Calculated Measure

I am trying to create a Running Sum which calculates the cumulative total of Calculated Measure.

I have done this before successfully on a Column using this pattern:

CumulativeSoldHours = CALCULATE( SUM(FV_Sold_Hours[Sold Hours]),
FILTER(
ALLSELECTED(FV_Sold_Hours),
FV_Sold_Hours[QC Date] <= MAX(FV_Sold_Hours[QC Date])
)
)

However if I try to use the above on a Measure I cannot because the SUM function requires a Column?

This was my attempt which fails because FV_Refurb_Revenue[Actual Refurb Revenue By QC Date] is a Measure and not a Column.

CumulativeRefurbRev = CALCULATE( SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]),
FILTER(
ALLSELECTED(FV_Refurb_Revenue),
FV_Refurb_Revenue[QC Complete DateTime] <= MAX(FV_Refurb_Revenue[QC Complete DateTime])
)
)

Any help much appreciated..

7 REPLIES 7 Frequent Visitor

Hi David,

Did you get a resolution to your problem (running total using a measure), as I have the same problem?  Helper I

Did you get a resolution to your problem (running total using a measure) because i have the same issue and i don't find a solution;  Microsoft

Hi @davidbrown100,

why do you use ALLSELECTED function in your formula, there is a slicer or filter on 'FV_Sold_Hours' table?

I think you'd better create a Calendar table and create a relationship between Calendar and your fact table.

```CumulativeRefurbRev = CALCULATE( SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]),
FILTER(
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)```

Best Regards,
Angelia  Helper II

Hi Angelia,

I am using ALLSELECTED because I have a slicer in the report, so I am happy with this bit.

There is also a Calendar table which I have named DIM_Time, however my Fact table has two dates, so I have used a measure to calculate the values from the other date field.  This in itself works fine.  The problem I am now having is that the examples for Running Totals seem to all involve using SUM which only works on a Field and not on a Measure.

My measure has the following expression (which works):

Actual Refurb Revenue By QC Date = CALCULATE(SUM(FV_Refurb_Revenue[Amount]),USERELATIONSHIP(FV_Refurb_Revenue[QC Complete DateTime],DIM_Time[TimeDate]))

Any further help appreciated as this has still got me stumped.

David.  Microsoft

Hi @davidbrown100

You can create a relationship between 'FV_Refurb_Revenue' and DIM_Time, then you replace SUM(FV_Refurb_Revenue[Amount]) to SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]) in "CumulativeRefurbRev" measure.

In your resource table, DIM_Time table is different from Calendar table?

Best Regards,
Angelia  Helper II

thanks for the response, however I cant do

SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date])

because Actual Refurb Revenue By QC Date is a calculated measure

This is the crux of my problem, how do you create running total on a calculated measure.  Microsoft

Hi @davidbrown100,

I got it. So I wish you post your sample data, and I see if I can integrate  the two measure into one, rather than creating running total on a calculated measure. I am unable to reproduce your scenario without data.

Best Regards,
Angelia Announcements #### Welcome to the User Group Public Preview #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (1,880)