TopN function cant display on the matrix

[Anonymous]

Hi, community, 

Please see the attached picture. I have a TOPN measure. I would like to get the top 10 customers based on the sales amount.

TOP10PCUSTOMERBYSALES1 = TOPN(10,VALUES(Orders[Customer ID]),SUM(Orders[Sales]),DESC)

When showing the measure in a table matrix, it gives the error below.

Above measure as illustrated in the book named "Exam Ref 70-778 analyzing and visualizing data by using Microsoft Power BI. It does not work in my case. 

If anyone can explain the logic and reason why it is wrong, also provide a solution that will be great. thank you!

[AllisonKennedy]

@Anonymous  It looks like you're trying to use a Table function inside a MEASURE, so TOPN is returning an entire table of values, but you need to get that down to 1 scalar expression, which is what @amitchandak 's suggestion will do for you. 

By putting the TOPN inside a filter expression of a CALCULATE, you are forcing the measure to only calculate for the TOPN values, but you still need to define what the measure actually is (ie SUM(Orders[Sales]). If you do this only, then you will get the same value for every customer, ie total sales for top 10 customers combined: 

TotalSalesforTop10Customers =

CALCULATE(SUM(Orders[Sales]), TOPN(10,VALUES(Orders[Customer ID]), SUM(Orders[Sales]), DESC))

IF you use a variable, then you can return each customer's total sales for only the Top10 customers:

Top10CustomersbySales =

VAR ranking= VALUES(Orders[Customer ID])

RETURN

CALCULATE(SUM(Orders[Sales]), TOPN(10,ALL(Orders[Customer ID]), SUM(Orders[Sales]), DESC),ranking)

Otherwise do your formula as a new TABLE rather than a MEASURE. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedy  thank you for the detailed explanation! that is very helpful for me to understand why... thanks again!

[AllisonKennedy]

You could also use the built in TOP N visual level filter on Customer ID (change the filter on Customer ID from Advanced or Basic to Top N) and drag Sales into the 'by value' field. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amitchandak]

@Anonymous , refer , if this can help

https://databear.com/power-bi-dax-topn-function/

TOP10PCUSTOMERBYSALES1 =
VAR
ranking = VALUES(Orders[Customer ID])
Return
CALCULATE(SUM(Orders[Sales]),
TOPN(10,ALL(Orders[Customer ID]),SUM(Orders[Sales])),
ranking,DESC)

For Rank Refer these links
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