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
6 years ago
Hi CoreyP

You need to do a similar measure to this:

Cumulative =
CALCULATE (
SUM ( TABLE[ONHAND QTY] );
FILTER ( ALLSELECTED ( TABLE[INDEX] ); TABLE[INDEX] <= MAX ( TABLE[INDEX] ) )
)
CoreyP's avatar
CoreyP
Solution Sage
6 years ago

This measure simply returns the same value.

 
Cumulative =
CALCULATE (
SUM ( 'Pick Sequence'[ONHAND_QTY] ),
FILTER ( ALLSELECTED ('Pick Sequence'[Pick Sequence] ), 'Pick Sequence'[Pick Sequence] <= MAX ( 'Pick Sequence'[Pick Sequence] )
))
  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi CoreyP ,

     

    Try the following change in the code:

    Cumulative =
CALCULATE (
SUM ( 'Pick Sequence'[ONHAND_QTY] ),
FILTER ( ALL ('Pick Sequence'[Pick Sequence] ), 'Pick Sequence'[Pick Sequence] <= MAX ( 'Pick Sequence'[Pick Sequence] )
))

     

     

    • CoreyP's avatar
      CoreyP
      Solution Sage
      6 years ago

      Unfortunately, that does not work either.

       

      I've kinda all but given up. I keep googling and re-writing and re-googling and re-googlewriting and now I just think it's a tad too advanced. Or, what's probably more likely, I'm not advanced enough to write it. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        Share the link from where i can download your PBI file.

  • MFelix's avatar
    MFelix
    Super User
    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.