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] )
    )
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Arr thought it was 100% but this woks if the slicer is Catrgory but not if it is any of the others slicers :(

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] )
    )
)