Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Cumulative sum sorted by another column in another table


Basically, I have one table with data zones and beneath them postcodes (multiple postcodes make up data zones). In another table, I have rows of sales details, which include postcodes. So, I can quite easily work out how many sales I have per data zone. My issue is I would like to know what the distribution of these sales are, and whether, say, 5% of datazones contribute 25% of sales.

My idea for doing this was to have a cumulative sum of sales, by datazone. So for example, if we had the following data (remembering that this is taken from two separate tables):

Data zone      Sales
DZ1               1
DZ2               2
DZ3               1
DZ4               4

Then I would like to see it sorted and counted like this:

Data zone     Cumulative total
DZ1               1
DZ3               2
DZ2               4
DZ4               8

However, when I have tried to do this, it has just sorted it by datazone, not number of sales-per-datazone, so I get this:

Data zone    Cumulative total
DZ1              1
DZ2              3
DZ3              4
DZ4              8

Has anyone come across this problem before? I know how I would do it in Excel but I can't seem to figure it out! Let me know if you need any further info

Community Support
Community Support

Hi  @HarryS ,


We can create a measure and sort by it to meet your requirement:


Cumulative total = 
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Sales] < MIN ( 'Table'[Sales] )
            || ( 'Table'[Data zone] <= MIN ( 'Table'[Data zone] ) && 'Table'[Sales] = MIN ( 'Table'[Sales] ))




Best regards,


Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


Thanks for your replies. Unfortunately I still can't seem to get it to work. I have solved the two tables problem by going back into Excel and creating a lookup column. I think part of the issue is probably that I presented an overly simplified account of my data. The relevant columns are as follows

Datazone                 Value
1                                1
2                                0
2                                0
3                                1
4                                1
5                                0
6                                0
3                                1
1                                1
3                                0

And so on. I would like to arrange the above table (on a much larger scale), in a cumulative total, ordered by total value, rather than Datazone number. Everything I have tried so far either keeps the Datazone number as the order and does a cumulative total based on that, or gives me wildly inflated figures.

Again, thanks for taking the time to reply and help me with this, I do appreciate it!

Super User
Super User

Hi @HarryS  

this sounds like an ABC-classification: 


Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.