Hello. I am working on a Pareto (80/20) Analysis. I have got a table which contains Supplier names and Spend values in columns. To calculate cumulative sum, I have used the following Measure:
Measure (Cumulative Spend) =
VAR TotalRevenue = CALCULATE(SUM('MyTable'[Spend]),ALLSELECTED('MyTable'))
VAR CurrentRevenue = SUM('MyTable'[Spend])
VAR SummarizedTable =
SUMMARIZE(
ALLSELECTED('MyTable'),
'MyTable'[Supplier],
"Revenue",SUM('MyTable'[Spend])
)
VAR CumulativeSum =
SUMX(
FILTER(SummarizedTable,[Revenue] >= CurrentRevenue),
[Revenue]
)
RETURN
DIVIDE(CumulativeSum,TotalRevenue)
Above measure is working perfect and it gives me the % cumulative spend by supplier - sorted High to Low by spend values.
Now I want to calculate the distinct count of suppliers which account for top 80% of the spend.
For example,
| Supplier | Spend | Cumulative Spend (using above Measure) |
| A | 500 | 36% |
| B | 250 | 54% |
| C | 200 | 68% |
| D | 200 | 82% |
| E | 150 | 93% |
| F | 100 | 100% |
As per the above table, cumulative spend exceeds 80% in 5th row. So, I need to have a visual/card to show how many suppliers account for that top 80% of the spend. (For above example, the answer should be 5 suppliers).
Also, the calculation should be dynamic ... that is, the DAX should work even if another filter is applied on the data. (For example, if there's another field "Countries" in the data and I filter for any particular country, the supplier count for top 80% should be recalculated based on that country ... and not the overall data).
Thank you.
