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
AltGr9
Helper I
Helper I

Cumulative Total Not Working Properly

Hello Power BI Community!

 

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

image.png

 

 

 

 

 

 

 

 

 

 

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:

image.png

 

 

 

 

 

 

 

 

 

 

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?

 

image.png

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
MartynRamsden
Solution Sage
Solution Sage

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

 

 

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.

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

Greatly appreciated! 

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.

Top Solution Authors