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
Arunraj89
Frequent Visitor

Displaying cards based on top 2 value dynamically

Hi guys,

 

I am facing a challenge in displaying two card visual. I have four columns in my dataset i.e Division, category, manufacturer and sales.

 

Division and category will be the slicers in my page. I have to shows top two manufacturers name along with the sum of sales in the card Visual based on the Division or category chosen.

 

One more criteria is that the client is not concerned with manufacturer M1. so, the ranking has to be calculated after leaving out M1. Could you please suggest me how to do it.

 

DivisionCategoryManufacturerSales
D1c1M1$156,579
D1c1M2$170,540
D1c2M1$200,047
D1c2M2$339,770
D2c3M3$188,716
D2c3M4$197,672
D2c4M1$228,519
D2c4M2$139,350
D3c5M5$205,832
D3c5M3$305,470
D3c5M1$386,465
D3c5M2$144,321
D3c6M1$273,366
D3c6M4$218,405
D4c7M5$272,252
D5c8M3$231,676

 

Thanks for the help @amitchandak @Greg_Deckler 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@Arunraj89 

 

To exclude M1 from the rank, use:

Manuf. Rank = 
VAR _RANK= RANKX(
             FILTER(ALLSELECTED('Fact Table'), 'Fact Table'[Manufacturer] <> "M1"), 
              [Sum Sales],,DESC,Dense)
RETURN
IF(ISINSCOPE('Fact Table'[Manufacturer]), _RANK)   // To Exclude from the "Total" row

 

Create your matrix, and exclude M1 in the filter for Manufacturer in the filter pane, and set the rank to "less than 3"

Result.JPG

 

I've included the PBIX file for you to check out.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

Hi @Arunraj89 ,

 

Have you resolved it? If yes, please accept the helpful answer as solution. Others will benefit from this thread. Thanks! If you still need help, please feel free to ask us.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PaulDBrown
Community Champion
Community Champion

@Arunraj89 

 

To exclude M1 from the rank, use:

Manuf. Rank = 
VAR _RANK= RANKX(
             FILTER(ALLSELECTED('Fact Table'), 'Fact Table'[Manufacturer] <> "M1"), 
              [Sum Sales],,DESC,Dense)
RETURN
IF(ISINSCOPE('Fact Table'[Manufacturer]), _RANK)   // To Exclude from the "Total" row

 

Create your matrix, and exclude M1 in the filter for Manufacturer in the filter pane, and set the rank to "less than 3"

Result.JPG

 

I've included the PBIX file for you to check out.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

@Arunraj89 , As per what I got

you can create a rank

Total sales = sum(Table[Sales])
Rank = RankX(allselected(Table[Manufacturer]),[Total sales])

You can append and show Manufacturer and sales. or you can put the Manufacturer on one card and sales on another. The filter then Rank =1 . And use a group card to show them together

 

same for Rank 2

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-visuals

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

 

top Manufacturer
maxx(filter(Values(Table[Manufacturer]),[Rank]=1),Table[Manufacturer])

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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