cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Continued Contributor

## Product Ranking in Total Group Values?

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)``

Sampel Data

Can you please suggest workaround to fix this issue?

Regards

Bvk

1 ACCEPTED SOLUTION
Super User

Hi @itsmebvk ,

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

5 REPLIES 5
Super User
Continued Contributor

@Ahmedx  This is another great solution to rank based on lowest level. Really appreciate your help on this.

Continued Contributor

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.

Super User

Hi @itsmebvk ,

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

Continued Contributor

@MohammadLoran25  This worked like a charm. Thanks alot for quick inputs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors