Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Total Issue

Hello,   When I want to make cumulative total for my table that you can see below, my formula returns wrong cumulative total for each distributor row. Can you help me to solve this issue ? (What I ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

     

    Try:

     

     

     

    RANKX % Sum ratio = RANKX(ALL(table[DistName]); [Sum Ratio]; ;DESC)

     

     

     

     

    Followed by:

     

     

     

    Cumulative % Sum Ratio = SUMX(TOPN([RANKX % Sum ratio]; ALL(table[DistName]); [Sum Ratio]); [Sum Ratio])

     

     

     

    And to get rid of the total:

     

     

     

    Cumulative without total in matrix = IF(ISINSCOPE(table[DistName]); [Cumulative % Sum Ratio]; BLANK())

     

     

Tahreem24's avatar
Tahreem24
Super User
6 years ago

Hi Anonymous ,

 

Please give a try to below DAX Measure:

 

 

Cumulative Sum=
CALCULATE (
    SUM ( 'Table'[Sum Ratio] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[DateColumn] <= MAX ( 'Table'[DateColumn] )
    )
)

Please give Kudos this effort and mark it as a solution if it helps!!!

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dear Tahreem24 ,

 

Unfortunately formula you type does not work. You can see the result below :

 

Apart from that, why do I need to use date formula ?

 

Best Regards.

 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You are using calendar date, I think you should use DistName

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dear amitchandak ,

       

      When I use distname column in seller, output is below :

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

         If possible please share a sample pbix file after removing sensitive information.
        Thanks

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Anonymous ,

     

    Date field is used to check the prevoius date so accordingly it will add up your Sum Ratio value.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Tahreem24 

       

      But I do not want to see cumulative total based on date, I just want to see it based on from large to small in distributor level.