cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
Super User IX

Re: Calculate Revenue for Top N Clients within each industry

3 REPLIES 3
Highlighted
Super User V
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
Super User IX

Re: Calculate Revenue for Top N Clients within each industry

Highlighted
Microsoft
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:

1.png

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

please upload some data samples and expected output. And please do mask sensitive data before uploading.

 

Best Regards,

Eyelyn Qin

Helpful resources

Announcements
Ignite

Microsoft Ignite

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

Get Ready for Power BI Dev Camp

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