Finding top5 customers in each country based on amount

[Anonymous]

I need to find the top5 customers based on each country, but so far, I have only been able to get/visualize the top5 customers based on all countries instead of getting the top5 customer from each country. I am tryint to visualize it in a matrix. Also, I have tried to create a measure and in the filter options used the TopN filter function to only show top 5.

Right now my result looks like this:

Germany	Amount
x1	10000
x2	1000
France
x1	10000
x2	1000
Netherlands
x1	10000

But I want the results to be like this:

Germany	Amount
x1	10000
x2	1000
x3	100
x4	10
x5	1
France
x1	10000
x2	1000
x3	100
x4	10
x5	1
Netherlands
x1	10000
x2	1000
x3	100
x4	10
x5	1

Hope you can help and looking forward to it.

BR

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You will need a measure that just calculates the sales amount.

Sales Amount = SUM ( Sales[Amount] )

Then you can use another measure to calculate your top 5 customers by country.

Top 5 Customer Sales = 
VAR RankingContext = VALUES ( Customer[Name] )
RETURN
CALCULATE(
    [Sales Amount],
    TOPN ( 5, ALL ( Customer[Name] ), [Sales Amount] ),
    RankingContext
)

[jdbuchanan71]

@HGINEV 

For that we need to tell our calculation to ignore the filters on the Customer table, where the country is coming from, when selecting the top 5 so we can do it like this.

TOP5_PRODUCTS = 

VAR _TOPN =
    CALCULATETABLE (
        TOPN ( 5, ALL ( 'Product'[Name] ), [Sales Amount] ),
        ALL ( Customer[Country] )
    )
RETURN
    CALCULATE ( [Sales Amount], KEEPFILTERS ( _TOPN ) )

[HGINEV]

Thank you for the quick reply, @jdbuchanan71 ! Unfortunately, the code doesn't work 100% for me. I receive the TOP5 products for ALL countries. However I have a slicer on the dashboard where I can choose from the (Customer [Country]). Now when I choose all countries everything is correct. However if I choose Germany in the slicer I see only top 2 products. To sum up the previous code was working for separate country (e.g. for DE I was able to see 5 codes) but for all countries, I wasn't seeing the top 5, but I was seeing 15 records (5 per each country). Now I am seeing top 5 for all countries but for separate countries I see 1-2 products (e.g. DE I see only 2).

I guess I need a mixture of both solutions. Somehow I need to ignore the dimension Countries in the measure but at the same time to be applicable in the filter somehow.

[jdbuchanan71 · Accepted solution]

@HGINEV 

Easier than you might think, we just need to use ALLSELECTED instead of ALL for country like this.

TOP5_PRODUCTS = 

VAR _TOPN =
    CALCULATETABLE (
        TOPN ( 5, ALL ( 'Product'[Name] ), [Sales Amount] ),
        ALLSELECTED ( Customer[Country] )
    )
RETURN
    CALCULATE ( [Sales Amount], KEEPFILTERS ( _TOPN ) )

[HGINEV]

@jdbuchanan71 Thank you so much for your help! Now everything works fine! 

[HGINEV]

Hello,

I am quite new to Power BI. I am having the other way around issue. I want to see only the top 5 products by sum of sales. I have a filter where I can choose different locations. When I choose ALL ( I see 15 products for Germany, France and Netherlands). I want to see only 5 products no matter the country. Something like this:

Germany	Amount
x1	10000
x2	1000
France
x1	10000
x2	1000
Netherlands
x1	10000

Currently my DAX looks like this:

TOP5_PRODUCTS = 

VAR

      RankingContext = Values(Product[Name])

RETURN

CALCULATE([Sales_Amount],

       TOPN(5, ALL(Product[Name]),[Sales_Amount],RankingContext)

Just an additional note, I want to segment by countries as well like in the table example.

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You will need a measure that just calculates the sales amount.

Sales Amount = SUM ( Sales[Amount] )

Then you can use another measure to calculate your top 5 customers by country.

Top 5 Customer Sales = 
VAR RankingContext = VALUES ( Customer[Name] )
RETURN
CALCULATE(
    [Sales Amount],
    TOPN ( 5, ALL ( Customer[Name] ), [Sales Amount] ),
    RankingContext
)

[Anonymous]

Hi @jdbuchanan71

Thanks for quick reply!

Even when I use your calculations I still get the same problem with only 5 shown in total across all countries. Has this something to do with that my Country is listed in one dimension table, Customer names in another dimension name and total amount in a fact table?

[jdbuchanan71]

@Anonymous 

Did you remove the TopN filter from the visual? 

Having multiple tables involved should work fine.  Here is the same measure with Category from one table, Name from one table ane my sales amount from a thrid table.

[Anonymous]

@jdbuchanan71 

No stupid me!

Your solution works perfect. Thank you very much!

[Anonymous]

@jdbuchanan71 is it possible to modify your DAX code, so it is still showing top5 customers based on sale but now also show columns with how many pcs the customer sold, index number, last year sale, pcs sold last year etc.?

[jdbuchanan71]

It is, yes.  You just have to change which measure is being calculated.  You still want the ranked on the sales amount though:

Top 5 Customer Sales = 
VAR RankingContext = VALUES ( Customer[Name] )
RETURN
CALCULATE(
    [CHANGE THIS MEASURE],
    TOPN ( 5, ALL ( Customer[Name] ), [Sales Amount] ),
    RankingContext
)

[Anonymous]

@jdbuchanan71  would I need to create a new measure for every column I want added to the matrix showing top 5 customers based on sales (index, order placed, number of pieces etc.) or could I just add these to your DAX code where you have written "Change this measure"?

[jdbuchanan71]

@Anonymous , you would need a new measure for each value you want to display.

[Anonymous]

@jdbuchanan71 Okay, thank you very much for your help.