Double Cumulative Pareto Chart sorted by incline

[bkr]

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

[Anonymous]

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])))

[bkr]

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.

[Anonymous]

@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