cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Responsive Resident

## 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 ,

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

5 REPLIES 5
Super User
Responsive Resident

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

Responsive Resident

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.

Super User

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

Responsive Resident

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

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors