cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidbrown100
Helper II
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
dax_bee
Frequent Visitor

Hi David,

 

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

 

Thanks In Advance.

Hi @davidbrown100

 

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;

thanks for your help.

v-huizhn-msft
Microsoft
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] )
     )
)


More details, please review the DAX in this article.

If this doesn't resolve your issue, please share your sample data table for further analysis.

Best Regards,
Angelia

Hi Angelia,

Thanks for your response.

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.

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

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.

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 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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