topic Re: Top N products by highest score in DAX Commands and Tips

Top N products by highest score

Anonymous — Mon, 24 Oct 2022 15:38:01 GMT

Hello!

I have a table with products and their rating score, like this:

I want to show the top 10 products according to their highest score.

I am struggling to find the correct dax that will return me the highest score for each distinct product, and then use it in a visualisation. Top N doesn't show the top 10 products either, it will only arrange the full Product list in to descending order when used in a visualisation.

Does anyone know the simplest way to do this?

Thank you in advance.

Re: Top N products by highest score

johnt75 — Mon, 24 Oct 2022 16:12:54 GMT

Create a measure like

Highest score = MAX( 'Table'[Score])

you can then put that into a table visual with the product and use it as a TOPN filter in the filter pane

Re: Top N products by highest score

Anonymous — Mon, 24 Oct 2022 17:39:43 GMT

Hello,
first start by created the max measure:

max of eac prodcut = CALCULATE(MAX(Bob[Score]),ALLEXCEPT(Bob,Bob[Product]))

now you have to rank your scores:

ranking based on max = RANKX(ALL(Bob[Product]),[max of eac prodcut],,DESC,SKIP)

and to pick your top 10 :

Top 10 products = CALCULATE([max of eac prodcut],FILTER(Bob,[ranking based on max]<=10))

or just go to filters,add filetrs topn and put the max of each product measure as values

Re: Top N products by highest score

v-yadongf-msft — Thu, 27 Oct 2022 09:10:12 GMT

Hi Anonymous ,

Please create a new column:

RANK = RANKX('Table',[Sore],,DESC,Skip)

Create a table and put RANK field into filters, you can show the top 10 products according to their highest score.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.