Forum Discussion

Christian_DC's avatar
Christian_DC
Regular Visitor
9 years ago
Solved

Cumulative % using Index not dates in DAX without measures

Hello Everybody,

 

Thanks for taking the time to read my question.

 

I am trying to assign a cumulative % to each row of customer data based on descending values, so that I can then add another column to label each customer 'high value' (top 80% by value), 'medium value' (next 15%) or 'low value' (final 5%).  I have tried to create this using DAX as follows:

 

Running_%

 

(In case the above picture hasn't come through properly, the column formula looks like this:)

 

Running % =
CALCULATE (
SUM ( 'Customer level'[Value %] ),
FILTER (
ALL ( 'Customer level'[Index.1] ,
'Customer level'[Index.1] <= MAX ( 'Customer level'[Index.1] )
)
)
)

 

I have added an Index column to give a rank but the above formula does not work.

 

Please help!

 

:-)

Need Help
  • Christian_DC's avatar
    Christian_DC
    9 years ago

    Thanks Tom.

     

    I actually just got it to work by modifying my original column formula to, i.e. using 'EARLIER' instead of 'MAX' :

     

    Running % =
    CALCULATE (
        SUM ( 'Customer level'[Value %] ),
        ALL ( 'Customer level' ),
        'Customer level'[Index.1] <= EARLIER ( 'Customer level'[Index.1] )
    )

     

    Incidentally, I have subsequently tried your method but haven't quite got it to work, but I won't trouble you further.

     

    Many thanks for your quick reply!

     

    Christian

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    I think if you rewrite your calculated column in this way, it should work

    Running % =
var currentIndex =  'Customer level'[Index.1]
return
CALCULATE (
SUM ( 'Customer level'[Value %] ),
FILTER (
  ALLEXCEPT('Customer level', 'Customer    level'[nameofyourcustomercolumn]) ,
'Customer level'[Index.1] <= currentIndex 
)
)

    Hope this helps, if not please provide some sample data.

     

    Regards

    • Christian_DC's avatar
      Christian_DC
      Regular Visitor
      9 years ago

      Thanks Tom.

       

      I actually just got it to work by modifying my original column formula to, i.e. using 'EARLIER' instead of 'MAX' :

       

      Running % =
      CALCULATE (
          SUM ( 'Customer level'[Value %] ),
          ALL ( 'Customer level' ),
          'Customer level'[Index.1] <= EARLIER ( 'Customer level'[Index.1] )
      )

       

      Incidentally, I have subsequently tried your method but haven't quite got it to work, but I won't trouble you further.

       

      Many thanks for your quick reply!

       

      Christian

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        Hey,

         

        great you figured it out, but I would be a little hesitant to use

        FILTER(ALL('table'), ...)

        in my little sample data this leads to this result

         

        Edited just realized that the column "Using Earlier" should be named "Using ALL(table)"

        But maybe I was wrong in understanding your question not to create a cumulative sum for each customer separately.

         

        Regards