cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User II
Super User II

@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
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.
Super User II
Super User II

@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

Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors