Forum Discussion

AltGr9's avatar
AltGr9
Icon for Helper I rankHelper I
6 years ago
Solved

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:                     This shows me e...
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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

MartynRamsden's avatar
MartynRamsden
Icon for Solution Sage rankSolution Sage
6 years ago

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

 

 

  • AltGr9's avatar
    AltGr9
    Icon for Helper I rankHelper I
    6 years ago

    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.

    • MartynRamsden's avatar
      MartynRamsden
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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

      • AltGr9's avatar
        AltGr9
        Icon for Helper I rankHelper I
        6 years ago

        Greatly appreciated!