cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Pareto - Distinct Count of Top 80% Vendors

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.

2 REPLIES 2
Highlighted
Post Prodigy

@abdulmannan1991  This post describes exactly what you are looking for in detail.

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

Highlighted

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors