Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Liviachia
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.

SKUsMADAccuracy
Product A4095%
Product B5580%
Product C6075%
Product D8060%
Product E12030%
Product F20015%
Product G00
Product H8030%
Product X2040%
2 ACCEPTED SOLUTIONS

Hi @Liviachia ,

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

vzhouwenmsft_0-1713434374036.png

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

 

vzhouwenmsft_2-1713434625671.png

vzhouwenmsft_3-1713434647607.png

 

View solution in original post

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")))
 
11.PNG
 
then you use the column to filter top and bottom 
12.PNG




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

Proud to be a Super User!




View solution in original post

8 REPLIES 8
v-zhouwen-msft
Community Support
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:

vzhouwenmsft_0-1713423072168.png

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

vzhouwenmsft_1-1713423422639.png

vzhouwenmsft_2-1713423441305.png

 


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.

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

 

Liviachia_0-1713432409662.png

 

Hi @Liviachia ,

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

vzhouwenmsft_0-1713434374036.png

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

 

vzhouwenmsft_2-1713434625671.png

vzhouwenmsft_3-1713434647607.png

 

ryan_mayu
Super User
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!




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

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!




I expect the following

 

Top 3

Liviachia_1-1713432599826.png

 

Bottom 3

Liviachia_2-1713432619822.png

 

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")))
 
11.PNG
 
then you use the column to filter top and bottom 
12.PNG




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

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.