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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Finding top5 customers in each country based on amount

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:

GermanyAmount
x110000
x21000
France 
x110000
x21000
Netherlands 
x1

10000

 

But I want the results to be like this:

GermanyAmount
x110000
x21000
x3100
x410
x51
France 
x110000
x21000
x3100
x410
x51
Netherlands 
x110000
x21000
x3100
x410
x51

 

Hope you can help and looking forward to it.

 

BR

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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
)

Top5Customers.jpg

View solution in original post

@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 ) )

View solution in original post

14 REPLIES 14
jdbuchanan71
Super User
Super User

@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 ) )

 

 

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.

@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 ) )

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

HGINEV
Frequent Visitor

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:

 

GermanyAmount
x110000
x21000
France 
x110000
x21000
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
Super User
Super User

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
)

Top5Customers.jpg

Anonymous
Not applicable

Hi @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.

Top5CustCategory.jpg

Anonymous
Not applicable

@jdbuchanan71 

 

No stupid me!

 

Your solution works perfect. Thank you very much!

Anonymous
Not applicable

@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.?

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
Not applicable

@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"?

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

Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.