Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

     

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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-with-Winners/ba-p/890814

     

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

     

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

     

    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.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi Anonymous ，

     

    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.