Show top/bottom 3 SKUs considering 2 column criteria in Power BI

[Liviachia]

Hi, anyone know how to show top / bottom 3 SKUs by considering the accuracy % and MAD volume in Power BI chart.

Creteria for Top/Bottom as follow:

• Top 3 SKUs, the creteria is using the highest accuracy % and the lowest MAD
• Bottom 3 SKUs, the creteria is low accuracy and high MAD

I tried the TopN, but it only allowed me to choose based on 1 creteria.  not sure if it can be use for both creteria. Another thing is, I would like to exclude SKUs with 0 value.

SKUs	MAD	Accuracy
Product A	40	95%
Product B	55	80%
Product C	60	75%
Product D	80	60%
Product E	120	30%
Product F	200	15%
Product G	0	0
Product H	80	30%
Product X	20	40%

[v-zhouwen-msft · Accepted solution]

Hi @Liviachia ,

Regarding your question, are you creating a measure? The creation should be a table.

If you want to filter the visual objects, do the following.

Table 3 = ADDCOLUMNS(VALUES('Table'),"Rank",RANKX('Table 2',[Accuracy],,,Dense) * 100  +  RANKX('Table 2',[MAD],,ASC,Dense))

[v-zhouwen-msft]

Hi @ryan_mayu ,thanks for the quick reply, I'll add further.

Hi @Liviachia ,

Regarding your question, my understanding is that the value in 'Accracy' is the main determinant of the ranking, and only in the case of the same value in 'Accracy', the value in 'MAD' is used for the secondary ranking.

The Table data is shown below:

Please follow these steps:

1. Use the following DAX expression to create a table

Table 2 = FILTER('Table','Table'[MAD] <> 0)

2.Use the following DAX expression to create a column in 'Table2'

Column = RANKX('Table 2',[Accuracy],,,Dense) * 100  +  RANKX('Table 2',[MAD],,ASC,Dense)

3.Final output

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

[Liviachia]

I tried this but look like the formula is not working.  Anyway to do the filtering in the visual instead of my Query?

[v-zhouwen-msft · Accepted solution]

Hi @Liviachia ,

Regarding your question, are you creating a measure? The creation should be a table.

If you want to filter the visual objects, do the following.

Table 3 = ADDCOLUMNS(VALUES('Table'),"Rank",RANKX('Table 2',[Accuracy],,,Dense) * 100  +  RANKX('Table 2',[MAD],,ASC,Dense))

[ryan_mayu]

is this your raw data or table visual?

what's the expected output?

[Liviachia]

Raw data and I want to show it in table visual as top 3 and bottom 3

[ryan_mayu]

what's the expected output based on the sample data you provide?

what do you mean  highest accuracy % and the lowest MAD?

what if it has a high accuracy% and high  MAD ?

[Liviachia]

I expect the following

Top 3

Bottom 3

[ryan_mayu · Accepted solution]

pls see if this is what you want

Column = if('Table'[Accuracy]=0,"",if(rankx(FILTER('Table','Table'[Accuracy]<>0),'Table'[Accuracy])<=3,"top",if(rankx(FILTER('Table','Table'[Accuracy]<>0),'Table'[Accuracy],,ASC)<=3,"bottom")))

then you use the column to filter top and bottom