March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Experts,
I am trying to create a report where I want to show selected product ranking based on slicer selection, along with that i also want to show total product count with in the group which the selected product belongs to.
For example : As shown below when I selected candy in slicer my card below should display as "3/3", because Food Group have 3 products out of that candy ranking is 3. (please see attached sample data and file).
Expression mentioned below not considering groups.
Rank = RANKX(ALL(Dim),CALCULATE(SUM('Fact'[Qty]),ALLEXCEPT(Dim,Dim[Product])),,DESC,Dense)
Can you please suggest workaround to fix this issue?
Regards
Bvk
Solved! Go to Solution.
Hi @itsmebvk ,
Follow the steps Below:
1-Create a measure:
QuantityMeasure = SUM('Fact'[Qty])
2-The Create this one for your count:
CountofProductsMeasure =
CALCULATE (
COUNTROWS ( VALUES ( Dim[Product] ) ),
FILTER ( ALL ( DIM ), Dim[Group] = SELECTEDVALUE ( Dim[Group] ) )
)
3-Then this one for your Ranking:
RankMeasure =
CALCULATE (
RANKX (
FILTER (
SUMMARIZE ( ALL ( Dim ), Dim[Product], Dim[Group] ),
Dim[Group] = SELECTEDVALUE ( Dim[Group] )
),
[QuantityMeasure]
)
)
4-And the last step:
ConcatenatedMeasure = "''" &[CountofProductsMeasure] & "/" & [RankMeasure] & "''"
If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhygwAEFHfo9FOOTY?e=tsh0vA
@Ahmedx This is another great solution to rank based on lowest level. Really appreciate your help on this.
thanks alot @Ahmedx for your reply.
Apologies in the sample data i provided I have given only two levels dimensions, but in real data another I have another level below Product. because of that when i follow the same method you suggested its giving ranking based on the lowest level. Can you please suggest how we can alter this? I need same output based on Product level. not subcategory level.
Please find the attched updated file.
Hi @itsmebvk ,
Follow the steps Below:
1-Create a measure:
QuantityMeasure = SUM('Fact'[Qty])
2-The Create this one for your count:
CountofProductsMeasure =
CALCULATE (
COUNTROWS ( VALUES ( Dim[Product] ) ),
FILTER ( ALL ( DIM ), Dim[Group] = SELECTEDVALUE ( Dim[Group] ) )
)
3-Then this one for your Ranking:
RankMeasure =
CALCULATE (
RANKX (
FILTER (
SUMMARIZE ( ALL ( Dim ), Dim[Product], Dim[Group] ),
Dim[Group] = SELECTEDVALUE ( Dim[Group] )
),
[QuantityMeasure]
)
)
4-And the last step:
ConcatenatedMeasure = "''" &[CountofProductsMeasure] & "/" & [RankMeasure] & "''"
If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
86 | |
69 | |
61 |
User | Count |
---|---|
140 | |
121 | |
108 | |
99 | |
96 |