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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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])

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.