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.

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