cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Christian_DC Frequent Visitor
Frequent Visitor

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!

 

🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Christian_DC Frequent Visitor
Frequent Visitor

Re: Cumulative % using Index not dates in DAX without measures

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

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Cumulative % using Index not dates in DAX without measures

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Christian_DC Frequent Visitor
Frequent Visitor

Re: Cumulative % using Index not dates in DAX without measures

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

View solution in original post

Highlighted
Super User
Super User

Re: Cumulative % using Index not dates in DAX without measures

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

using all table.png

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

 

Regards

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)