Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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.
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.
Proud to be a Super User!
Paul on Linkedin.
@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
top Manufacturer
maxx(filter(Values(Table[Manufacturer]),[Rank]=1),Table[Manufacturer])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |