Dax - add column - group then rank

[MichaelF1]

Hi everyone

I need to add a Rank Column to a table.

I have this:

Date	Product Name	Num Sold
Date1	Prod1	2
Date2	Prod1	6
Date3	Prod2	6
etc	Prod2	4
etc	Prod1	10
etc	Prod2	2
etc	Prod1	8
etc	Prod2	6
etc	Prod2	9

And I want to add a column that returns the Rank of the product, based on the total number sold for each product across all dates.

Like this:

Date	Product Name	Num Sold	Product rank
Date1	Prod1	2	2
Date2	Prod1	6	2
Date3	Prod2	6	1
etc	Prod2	4	1
etc	Prod1	10	2
etc	Prod2	2	1
etc	Prod1	8	2
etc	Prod2	6	1
etc	Prod2	9	1

I've been trying this:

Product rank =

RANKX(

    SUMMARIZE(

        'MyTable',  

        'MyTable'[Product Name], "GroupedQty", SUM('MyTable'[Num Sold]))

    ,  

    [GroupedQty],  

    ,  

    ASC

)

But I get an error saying: "Column 'GroupedQty' cannot be found or may not be used in this expression"

Thanks in advance 🙂

[AnalyticsWizard]

@MichaelF1 

The error you’re encountering is because the SUMMARIZE function creates a new table, and the column GroupedQty is not recognized outside of this function.

To create a rank column based on the total number sold for each product across all dates, you can use the RANKX function in DAX. Here’s an example:

Product rank = 
RANKX(
    ALL('MyTable'[Product Name]),
    CALCULATE(SUM('MyTable'[Num Sold]))
)

This formula ranks the products based on the total number sold. The ALL function is used to consider all the products in the calculation, and the CALCULATE function changes the context in which the sum is computed.

Please replace 'MyTable' with the actual name of your table. This measure will create a new column in your table that contains the rank of each product based on the total number sold.

Remember, DAX operates on tables and columns, not on individual cells or rows. 

[MichaelF1]

Hi @AnalyticsWizard , thanks for your post, but I don't seem to get the right result from this code:

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Total of product = CALCULATE(SUM(Data[Num Sold]),FILTER(data,Data[Product Name]=EARLIER(Data[Product Name])))

Rank = CALCULATE(DISTINCTCOUNT(Data[Total of product]),FILTER(Data,Data[Total of product]>=EARLIER(Data[Total of product])))

Hope this helps.

[MichaelF1]

Hi, yes this worked, thank you. Although I'm surprised to see that you got the rank without using any of the Rank functions!

[Ashish_Mathur]

You are welcome.

[lbendlin]

Yes, RANKX expects a distinct list of category values.  You need to first aggregate your data, and then backport the ranking into the individual rows

Product Rank =
VAR p = [Product Name]
VAR b =
    ADDCOLUMNS (
        ALL ( MyTable[Product Name] ),
        "s",
            VAR p2 = [Product Name]
            RETURN
                SUMX ( FILTER ( MyTable, [Product Name] = p2 ), [Num Sold] )
    )
RETURN
    MAXX (
        FILTER ( ADDCOLUMNS ( b, "r", RANKX ( b, [s] ) ), [Product Name] = p ),
        [r]
    )

or if you want to live on the edge

Product Rank =
VAR p = [Product Name]
VAR b =
    SUMMARIZE ( ALL ( MyTable ), [Product Name], "s", SUM ( MyTable[Num Sold] ) )
RETURN
    MAXX (
        FILTER ( ADDCOLUMNS ( b, "r", RANKX ( b, [s] ) ), [Product Name] = p ),
        [r]
    )

[Bipin-Lala]

Hi @MichaelF1,

I was able to achieve your expected result by using the following DAX formulas below - 

Summing Product Numbers Sold

Total Units Sold = CALCULATE(SUM('Products'[Num Sold]), ALLEXCEPT(Products, Products[Product Name]))

 Calculating Rank of each product 

Product Rank = RANKX(ALL(Products[Product Name]), [Total Units Sold], , DESC, Dense)

Note: If you don't use ALLEXCEPT during summation, then RANX won't return the expect rank when you display multiple fields in the visual, like dates.

Total Units Sold = CALCULATE(SUM('Products'[Num Sold]))

Correct Rank

Incorrect Rank as soon as dates field is added

Let me know if this helps!

[MichaelF1]

Hi @Bipin-Lala , Thanks for your post. Is your solution for adding columns to a visual? I really want to add the columns to a table. I seem to get the incorrect rank: