Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
3 REPLIES 3
v-eqin-msft
Community Support
Community Support

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:

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

lbendlin
Super User
Super User

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/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.