cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Calculate Revenue for Top N Clients within each industry

Hello - I would like help figuring out how to identify the top 5 clients for each industry and then summing these 5 clients revenues with the end goal of seeing what % of revenues these five clients comprise of the total industry revenue.

I will be displaying this in a table, where each row is a different industry (client names will not be shown).  Note:  There are multiple data rows for each client so revenues will need to be summed.  Some clients may be in the top 5 for multiple industries.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: Calculate Revenue for Top N Clients within each industry

@DataNewbie , refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Also, refer subcategory Rank

Try TOPN

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User V

## Re: Calculate Revenue for Top N Clients within each industry

This is the "Top x and other bucket"  pattern.   Usually you use RANKX for that, with a bit of a twist.  The article here shows how.

https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/

Highlighted
Super User IX

## Re: Calculate Revenue for Top N Clients within each industry

@DataNewbie , refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Also, refer subcategory Rank

Try TOPN

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

Proud to be a Super User!

Highlighted
Microsoft

## Re: Calculate Revenue for Top N Clients within each industry

Hi @DataNewbie ,

According to my understanding ,you want to calculate sum of the percentage of clients in the top five revenue sunders of different companies.
You could use the following formula:

//Calcalate rank based on each Industry's revenue and then set rank for visual-level filter

Ranking by Industry =
RANKX (
FILTER (
ALL ( ClientRevenue ),
'ClientRevenue'[Industry ] = MAX ( 'ClientRevenue'[Industry ] )
),
CALCULATE ( SUM ( 'ClientRevenue'[Revenues ] ) )
)

//Calculate %

sum per Industry =
CALCULATE (
SUM ( ClientRevenue[Revenues ] ),
ALLEXCEPT ( ClientRevenue, ClientRevenue[Industry ] )
)

% measure =
CALCULATE (
SUM ( ClientRevenue[Revenues ] ),
FILTER (
ClientRevenue,
ClientRevenue[Industry ] = MAX ( ClientRevenue[Industry ] )
)
) / [sum per Industry]

top5 total % =
SUMX (
FILTER (
ALL ( ClientRevenue ),
ClientRevenue[Industry ] = MAX ( ClientRevenue[Industry ] )
&& [Ranking by Industry] <= 5
),
[% measure]
)

My visualizations look like this:

Is the result what you want? Whether you want to calculate the % of top5/total or each in top5/top5?

Best Regards,

Eyelyn Qin

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors