Get 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |