Forum Discussion

itsmebvk's avatar
itsmebvk
Continued Contributor
3 years ago
Solved

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 wh...
Need Help
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    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's avatar
itsmebvk
Continued Contributor
3 years ago

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's avatar
MohammadLoran25
Solution Sage
3 years ago

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's avatar
    itsmebvk
    Continued Contributor
    3 years ago

     

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