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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dawn7047
Frequent Visitor

How to write a DAX expression where I can rank products within each category?

I have a query table as follows:

query table.PNG

For the DAX expression, I used a quick measure with the formula as follows:

ranking = RANKX(ALLSELECTED(Sheet1[products]),SUM(Sheet1[Current volume]))
 
The formula was supposed to Rank the products based on the Current Volume. To test this, I used a Pivot Table as the main visual and showed the values for Current Volume and Ranking side by side. The result were as follows:
visualpic.PNG
I want to show the ranking for each product based on the given category. Is there any DAX expression I can use to do this?
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1671516821158.png

 

 

Ranking measure expected result: =
SWITCH (
    TRUE (),
    ISINSCOPE ( Data[Products] ),
        RANKX (
            ALL ( Data[Products] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        ),
    ISINSCOPE ( Data[Category] ),
        RANKX (
            ALL ( Data[Category] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @Dawn7047 

the below screenshot shows the ranking for colors inside a brand:
image.png
Please be aware that also the Brands are ranked accordingly, I use this measure to create the above output:

Measure 4 = 
if( ISINSCOPE('DimProduct'[ColorName] )
    // the inner column (colorname) is ranked inside a group (brandname)
    , RANKX(
        CALCULATETABLE(
            SUMMARIZE(
                ALLSELECTED( 'FactOnlineSales' )
                , 'DimProduct'[BrandName]
                , 'DimProduct'[ColorName]
            )
            , VALUES( 'DimProduct'[BrandName] )
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesAmount] ) )
    )
    // the outer column (brandname) is ranked
    , RANKX(
        CALCULATETABLE(
            SUMMARIZE(
                ALLSELECTED( 'FactOnlineSales' )
                , 'DimProduct'[BrandName]
            )
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesAmount] ) )
    )
)

Hopefully this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1671516821158.png

 

 

Ranking measure expected result: =
SWITCH (
    TRUE (),
    ISINSCOPE ( Data[Products] ),
        RANKX (
            ALL ( Data[Products] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        ),
    ISINSCOPE ( Data[Category] ),
        RANKX (
            ALL ( Data[Category] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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