cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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%
2 ACCEPTED SOLUTIONS
Community Support

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

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

8 REPLIES 8
Community Support

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:

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.

Regular Visitor

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

Community Support

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

Super User

is this your raw data or table visual?

what's the expected output?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Regular Visitor

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

Super User

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 ?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Regular Visitor

I expect the following

Top 3

Bottom 3

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!