Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
itsmebvk
Continued Contributor
Continued Contributor

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

 

itsmebvamsi_1-1681450901020.png

 

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

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

 

View solution in original post

5 REPLIES 5
Ahmedx
Super User
Super User

itsmebvk
Continued Contributor
Continued Contributor

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

itsmebvk
Continued Contributor
Continued Contributor

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.

 

itsmebvamsi_0-1681474613870.png

 

Please find the attched updated file.

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

 

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.