Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling sum

Hi,   I am looking at creating a total sum per month as below in the Running Total. Year Region Cat Category MONTH Savings Running Total 2018 A XX Services ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try this as a MEASURE

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)

     

    or this as a CALCULATED COLUMN

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ),
        Targets[Date] <= EARLIER ( Targets[Date] )
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER

     

    Then Measure would be

     

    Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

When I use your data and use Region as slicer...It seemingly works well.

What slicer did you use?

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Arr yes but in my whole dataset it is a little different, I have an update example of what could happen.

 

As you can see from below Region Cat and Category can be different the was luck that Region Cat and Category lined up :(

 

That is why the sum always looked correct.

 

YearRegionCatCategoryMONTHSavingsRunning Total
2018AXXServicesJanuary5050
2018AXXServicesFebruary65115
2018BZZServicesMarch45160
2018AYY RetailJanuary2121
2019BXX RetailFebruary4566
2018BYY RetailMarch2389
2018BZZEquipmentJanuary1212
2018CZZEquipmentFebruary5567
2019CYYEquipmentMarch66133
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Adding to that I am using lots of slicers just the normal oob ones but Cat,Month,category etc..

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Anonymous

       

      I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER

       

      Then Measure would be

       

      Running_Total =
CALCULATE (
    SUM ( Targets[Savings] ),
    FILTER (
        ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]),
        Targets[Date] <= SELECTEDVALUE ( Targets[Date] )
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Great just updated my report and that dose seem to be the case thanks for all of your help