Measures for Top 25 and Bottom 25 customers (in terms of sales amount) in order to compare to rest

[herbemischung]

Hey guys,

Hey, guys, I'm not really getting anywhere right now.

I'm trying to create a measure that will give me the top 25 clients' revenue.

Then I want a second measure that gives me the sales of the bottom 25 customers.

At the end I want to calculate the difference:

Sales >Bottom25 <Top25 = [Total sales] - [sales top 25 customers] - [sales bottom 25 customers]

Thanks! all the best, herbert

[amitchandak · Accepted solution]

@herbemischung , refer if these can help, Change column and measure as per need

TopN Customer = 
VAR SelectedTop = 25
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Sales Amount],
    RANKX ( 
            ALLSELECTED(  'Customer'[Customer] ), 
            [Total Revenue], , desc,dense
                )
                  <= SelectedTop,
        [Sales Amount]
)


Bottom N Customer = 
VAR SelectedTop = 25
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Sales Amount],
    RANKX ( 
            ALLSELECTED(  'Customer'[Customer] ), 
            [Total Revenue], , asc,dense
                )
                  <= SelectedTop,
        [Sales Amount]
)

[amitchandak]

@herbemischung , You can get using TOP N or Rank

For Rank Refer these links

https://www.tutorialgateway.org/power-bi-top-10-filters/

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
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

[harshnathani]

HI @herbemischung ,

Create a measure

Total Revenue = SUM(Table[Revenue])

Top25 = CALCULATE([Total Revenue],TOPN(25,'Table',[Total Revenue],DESC)

Bottom25 = CALCULATE([Total Revenue],TOPN(25,'Table',[Total Revenue],ASC)

RETURN

Revenue - Top25 - Bottom25

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

@herbemischung ,

Total Revenue = SUMX(SalesTable,SalesTable[OrderQuantity]*related(Products[ProductPrice]))

Top25 = CALCULATE([Total Revenue],TOPN(25,'CustomerTable',[Total Revenue],DESC)

Bottom25 = CALCULATE([Total Revenue],TOPN(25,'CutomerTable',[Total Revenue],ASC)

RETURN

Revenue - Top25 - Bottom25

Regards,

HN

[herbemischung]

Hi, and thank you, but unfortunately it didn't work

I only get the total revenue, I guess because in your formula is no reference to the customer. 

[harshnathani]

@herbemischung ,

Please share your sample pbix file to help you better.

Table should be customer table.

Regards,

HN

[herbemischung]

Sorry, but I cannot share our company data. Thanks for trying!