cancel
Showing results for
Did you mean:
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:

(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.

🙂

1 ACCEPTED SOLUTION

Accepted Solutions
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.

Christian

3 REPLIES 3
Super User III

## 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
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
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.

Christian

Super User III

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

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

Regards

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors