Product Ranking in Total Group Values?

[itsmebvk]

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

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhygwAEFHfo9FOOTY?e=tsh0vA

[itsmebvk]

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

[itsmebvk]

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.

[MohammadLoran25 · Accepted 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

[itsmebvk]

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