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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |