Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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
Solved! Go to 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 )
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 ) )
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.
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 ) )
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.
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 )
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?
@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.
@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 )
@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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |