Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous

 

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
Employee
Employee

Hi @Anonymous,

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

Anonymous
Not applicable

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 @Anonymous,

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

Anonymous
Not applicable

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 @Anonymous,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.