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.
Dear Folks,
I'm struggling preparing a pareto chart in which both axes are cumulative and sorted by incline.
I start with the following calculated Summarytable:
Project | Sales | Cost |
A | 1000 | 500 |
B | 100 | 80 |
C | 300 | 150 |
D | 1000 | 400 |
What I want is:
1. Calculate how much sales I get for one dollar of costs. This is the incline.
2. Now sort the table by incline in a descending manner
3. Calculate cumulative values for both sales and Costs
Project | Sales | Cost | Incline = Sales/Cost | Cumulative Sales | Cumulative Cost |
D | 1000 | 400 | 2,5 | 1000 | 400 |
A | 1000 | 500 | 2 | 2000 | 900 |
C | 300 | 150 | 2 | 2300 | 1050 |
B | 100 | 80 | 1,25 | 2400 | 1130 |
Ultimately I want to prepare a double cumulative pareto chart sorted by incline.
The only thing I managed so far is to calculate the incline in my summarytable but I could not sort it and calculate the cumulative values thereafter.
Any helpful suggestions are appreciated
Hi @bkr
Here is my approach
Step 1: Create a custom column in Power Query
Incline = [Sales]/[Cost]
Step 2: Sort Incline column in desc order
Step 3: Add index column
Step 4: Create following measure
Cumulative Cost = CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])))
Cumulative Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])))
Hi Vimal,
thanks for your answer. I think the cumulative part should work your way, but I need to do this with an on the fly calculated summarytable. I cannot create this table in Power Query unfortunately.
@bkr Please try this approach
for incline and index column
Create calculated column instead of custom and index column in dax
Incline = DIVIDE('Table'[Sales],'Table'[Cost],0)
Index = RANKX('Table','Table'[Incline]+INT('Table'[Sales])/10000000,,DESC)
Please make sure you divide the sales by a large number so that there are no ties in rank calculation
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |