Displaying cards based on top 2 value dynamically

[Arunraj89]

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.

Division	Category	Manufacturer	Sales
D1	c1	M1	$156,579
D1	c1	M2	$170,540
D1	c2	M1	$200,047
D1	c2	M2	$339,770
D2	c3	M3	$188,716
D2	c3	M4	$197,672
D2	c4	M1	$228,519
D2	c4	M2	$139,350
D3	c5	M5	$205,832
D3	c5	M3	$305,470
D3	c5	M1	$386,465
D3	c5	M2	$144,321
D3	c6	M1	$273,366
D3	c6	M4	$218,405
D4	c7	M5	$272,252
D5	c8	M3	$231,676

Thanks for the help @amitchandak @Greg_Deckler 

[v-xuding-msft]

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.

[PaulDBrown · Accepted solution]

@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"

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

[amitchandak]

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