Calculate Revenue for Top N Clients within each industry

[Anonymous]

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!   

[v-eqin-msft]

Hi @Anonymous ,

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?

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

Best Regards,

Eyelyn Qin

[amitchandak · Accepted solution]

@Anonymous , refer

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

Also, refer subcategory Rank

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Try TOPN

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

[lbendlin]

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/