cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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,

SupplierSpendCumulative Spend (using above Measure)
A50036%
B25054%
C20068%
D20082%
E15093%
F100100%

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
Post Prodigy

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

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

Highlighted

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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