cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Cumulative sum sorted by another column in another table

Hello,

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

3 REPLIES 3
Community Support

Hi  @HarryS ,

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

``````Cumulative total =
CALCULATE (
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.
Helper I

Hello,

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

Hi @HarryS

this sounds like an ABC-classification: https://www.daxpatterns.com/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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors