cancel
Showing results for
Did you mean: Frequent Visitor

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

I have a query table as follows: 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: 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  Super User

Hi,

Please check the below picture and the attached pbix file. ``````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.

2 REPLIES 2  Super User

Hey @Dawn7047

the below screenshot shows the ranking for colors inside a brand: 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  Super User

Hi,

Please check the below picture and the attached pbix file. ``````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.  