Problem with RANKX with certain condition

DAX DAX Calculations
1

Hi,
I’ve got 2 issues about RANKX which need some help from the expert here.
No1 is I’m facing a same Ranking while actually my data displayed 2 different value.
My measure is like this :

Rank = 
VAR tmpVal = SUM('Sales'[Item qty])
RETURN
        IF(tmpVal = 0, BLANK(),
            RANKX(
                FILTER(
                        ALL(Products),
                        'Products'[Product category] = "A"
                        ),
                CALCULATE(
                            SUM('Sales'[Item qty])               
                ),,DESC,Dense
            )
        )

My intention on that measure is I want to rank my products but only products which categorized in my Product table as category “A”

But that measure result a same rank for 2 of my rows like below:
image
The column “item id” here is my field inside Product table.

No.2 is I also want to rank my Products (with the same category = “A”) but also filtering my transactions in which in my transactions has a special category, lets say because it is Sales Transaction, I have field SalesPool. I want to filter the sales transactions into only those with SalesPool=“West”. Question is can I just add the criteria inside the CALCULATE function ?

Please help.

Thanks

2

@Toni,

There are a number of different routes you could take here, but this is the approach I would use:

For your products, filter that table based using the filter pane, and for your Sales Pool, create a measure that calculates total quantity filtered by the transaction type you want.

I’ve created a parallel example using the Practice Dataset External tool. Here’s how it looks all put together:

image
image830×550 21.1 KB

Here are the key measures:

Rank Prod by Sales EXP = 

VAR vTable =
        ALLSELECTED( Products[Product Name] )

VAR Result =
IF( HASONEVALUE( Products[Product Name]),
RANKX(
    vTable, [Total Sales International],,DESC,Dense), BLANK() )

    RETURN
    Result

Total Sales International = 
CALCULATE(
    [Total Sales],
    Channels[Channel Code] = "EXP"
)

I hope this is helpful. Full solution file attached below.

– Brian

e DNA Forum – Ranking with Multiple Filter Conditions Solution.pbix (2.2 MB)

2 Likes
3

Hi BrianJ,

Thanks, it works!
But for my learning purposes, what’s wrong actually with my previous measures ? Seems not much different than yours, except for the ALLSELECTED which is to response the slicers. And if not mistaken, the HASONEVALUE is for cater the Grand Total to not having ranking as well, right ?

Many thanks for your help.

4

@Toni This:

5

@Toni,

Yes, you were quite close to the solution on this one. The changes made were:

  1. ALL to ALLSELECTED

  2. Addition of HASONEVALUE to avoid ranking on the total row

  3. Total Sales as a measure to avoid the need for the CALCULATE context transition

  4. The selection of the products to be included via Filter Pane, rather than DAX code. There are pros and cons of each approach, so the ultimate choice in this one will depend on your specific requirement

     Toni Rank = 

 VAR tmpVal =
         SUM( Sales[Line Total] )
     RETURN
         IF(
             tmpVal = 0,
             BLANK(),
             RANKX(
                 FILTER(
                     ALLSELECTED( Products ),
                     'Products'[Product Name]
                         IN { "Product 1", "Product 10", "Product 11", "Product 12", "Product 13" }
                 ),
                 CALCULATE( SUM( Sales[Line Total] ) ),
                 ,
                 DESC,
                 DENSE
             )
         )

image

I hope this is helpful.

  • Brian
1 Like