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

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

     

5 Replies

    • 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

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