topic Re: Cumulative Total Not Working Properly in DAX Commands and Tips

Cumulative Total Not Working Properly

AltGr9 — Wed, 11 Dec 2019 10:51:05 GMT

Hello Power BI Community!

I have a Fact table connected to a Month Dimension table using MonthID, and a running total showing Committed spend:

This shows me exactly what I want to see. I can see that the business was committed to 11,430.00 of spend until June (201906). Then in August they committed to an additional 15,870.60 spend, and in November (201911) they spent half of that 7,935.30. Great.

The problem is, I'm using the MonthID in the drill-down, and I don't want to because it looks ugly. I want it to look like the full month name, like this:

The problem, as you can see, is that now my cumulative total for Committed has disappeared! And yet all I've done is change the field from 'Dimension Month'[MonthID] to 'Dimension Month'[YearMonth].

My cumulative total for Committed is arrived at by subtracting my Received Running Total from my Ordered Running Total (as follows) - can anyone help me achieve a Committed running total with a full month name, as in the image above?

Re: Cumulative Total Not Working Properly

MartynRamsden — Wed, 11 Dec 2019 16:02:33 GMT

Hi AltGr9

Is the 'Month Name Alternative' column in your month dimension table sorted by another column?

If so, you'll need to include that in your measure too. Something like:

Ordered Running Total = VAR MaxMonthID = MAX ( 'Dimension Month'[Month ID] ) VAR Result = CALCULATE ( SUM ( 'Fact Monthly Project Rollup'[Capital Expenditure] ), ALL( 'Dimension Month'[Month Name Alternative], 'Dimension Month'[Month Name Alternative Sort] ), 'Dimension Month'[Month ID] <= MaxMonthID, 'Dimension Capital Expenditure Type'[Capital Expenditure Type] = "Ordered" ) RETURN Result

Best regards,

Martyn

Re: Cumulative Total Not Working Properly

AltGr9 — Wed, 11 Dec 2019 17:25:25 GMT

Thank you very much Martyn. The column was indeed sorted by another, and I added that to the ALL clause of both of the measures making up my Committed measure: [Ordered Running Total] and [Received Running Total].

Unfortunately, it didn't work. Same result as before.

Re: Cumulative Total Not Working Properly

MartynRamsden — Thu, 12 Dec 2019 09:21:21 GMT

Hi AltGr9

I think there must be another sort column in play (possibly on 'Dimension Month[YearMonth]).

Try the expression below, which will remove all filters from the 'Dimension Month' table apart from Month ID <= MaxMonthID:

Ordered Running Total = VAR MaxMonthID = MAX ( 'Dimension Month'[Month ID] ) VAR Result = CALCULATE ( SUM ( 'Fact Monthly Project Rollup'[Capital Expenditure] ), FILTER ( ALL ( 'Dimension Month' ), 'Dimension Month'[Month ID] <= MaxMonthID ), 'Dimension Capital Expenditure Type'[Capital Expenditure Type] = "Ordered" ) RETURN Result

Best regards,

Martyn

Re: Cumulative Total Not Working Properly

AltGr9 — Fri, 13 Dec 2019 09:22:46 GMT

Greatly appreciated!