Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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,
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!
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |