Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cummulative Sum responding to Slicer

Hi all,

 

I am trying to create a cummulative sum which goes with the slicer, meaning if I select the numbers: 3, it will retrieve the data all to the number 3.

 

Cumm = calculate(all(MS_CY[CY.NS3rd]);MS_CY[TimeBasis]="Periodic";MS_CY[UnitName]="Europe";MS_CY[Periodnumber]<Max(MS_CY[Periodnumber]))

 

It  doenst allow me saying i cant use MAX for a true or false function... really don´t know how to do so

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Measure1= CALCULATE(SUM(MS_CY[CY.NS3rd]),FILTER(ALLSELECTED(MS_CY), MS_CY[UnitName]=MAX(MS_CY[UnitName])&&MS_CY[Periodnumber]<=MAX(MS_CY[Periodnumber])&&MS_CY[TimeBasis]="Periodic"))

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous , 

     

    Do something like this where the you use var for the date, or number.

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    Total Cumulative hours = 
VAR MaxDate = MAX ( aver[Date] )-- Saves the last visible date
var Hours =
    CALCULATE (
        [Total Hours measure],            -- Computes hours
        aver[Date]<= MaxDate,   -- Where date is before the last visible date
        ALLEXCEPT(  aver,aver[Program] )               -- Removes any other filters from Date allexcept aver[Program]

   )
return Hours
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      No it doesnt work.

       

      I have an additional variable which is unit name. So basic i need to:

      1. Sum all values cummalative based on period numbers
      2. Filtered by:
      - Unit name (i must have a accumulated value by each unit name)
      3. Showing data only for
      - Periodic bases (it´s a columm which has periodic and MTD)

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Measure1= CALCULATE(SUM(MS_CY[CY.NS3rd]),FILTER(ALLSELECTED(MS_CY), MS_CY[UnitName]=MAX(MS_CY[UnitName])&&MS_CY[Periodnumber]<=MAX(MS_CY[Periodnumber])&&MS_CY[TimeBasis]="Periodic"))

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.