Forum Discussion

CoreyP's avatar
CoreyP
Solution Sage
6 years ago
Solved

Cumulative Total by Index

Hi Everyone,   Hoping you can help me write a measure for cumulative running total, but by an Index column rather than a date. I would like a measure for cumulative total of the ONHAND QTY column i...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi CoreyP ,

     

    This is a context problem, when you add the measure in your table since you have other columns within the visual and all of the fields belong to the same table the context changes so you need to apply the filter no only to the INDEX column but to all other columns in this case you need to redo your measure to:

     

    Cumulative =
CALCULATE (
    SUM ( 'Pick sequence'[ONHAND_QTY] );
    FILTER (
        ALLSELECTED (
            'Pick sequence'[Pick Sequence];
            'Pick sequence'[PALLET_ID];
            'Pick sequence'[Item N.º]
        );
        'Pick sequence'[Pick Sequence] <= MAX ( 'Pick sequence'[Pick Sequence] )
    )
)

     

    Check PBIX file attach I added a slicer for pallet s you can try to select different values and check that the cumulative still is calculated.

MFelix's avatar
MFelix
Super User
5 years ago

Hi BICrazy ,

 

Can you please send me a mockup?

BICrazy's avatar
BICrazy
Helper II
5 years ago

Hi MFelix  - sent you a pm with a link to onedrive, which contains the mockup

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi BICrazy ,

     

    You should use the following measure:

    Cumulative_Churn_1 = 
    SUMX (
        FILTER (
            SUMMARIZE (
                ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
                'Ongoing & Unpaid Data Revised'[Index],
                "@IndexChurn", [Previous Index Churn]
            ),
            'Ongoing & Unpaid Data Revised'[Index]
                <= MAX ( 'Ongoing & Unpaid Data Revised'[Index] )
        ),
        [@IndexChurn]
    )

     

     

    • BICrazy's avatar
      BICrazy
      Helper II
      5 years ago

      Hi MFelix ,

      This works like a charm.  Just that the 29517 on Index 0 is the base.  I'd like to start the count from Index 1 onwards. 

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi BICrazy ,

         

        Should the first value be 6982 or 36499?

         

        If you want to have the 6.982 then use the measure:

         

        Cumulative_Churn_ > 0 = 
    SUMX (
        FILTER (
            SUMMARIZE (
                ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
                'Ongoing & Unpaid Data Revised'[Index],
                "@IndexChurn", [Previous Index Churn]
            ),
            'Ongoing & Unpaid Data Revised'[Index]
                <= MAX ( 'Ongoing & Unpaid Data Revised'[Index] ) && 'Ongoing & Unpaid Data Revised'[Index] > 0
        ),
        [@IndexChurn]
    )

         

        If you don't want the 0 but want the basis sum use the following:

        Cumulative_Churn wthout 0 = 
    IF( MAX('Ongoing & Unpaid Data Revised'[Index]) <> 0, SUMX (
        FILTER (
            SUMMARIZE (
                ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
                'Ongoing & Unpaid Data Revised'[Index],
                "@IndexChurn", [Previous Index Churn]
            ),
            'Ongoing & Unpaid Data Revised'[Index]
                <= MAX ( 'Ongoing & Unpaid Data Revised'[Index] ) 
        ),
        [@IndexChurn]
    ))