How to Rank the total sales partition by category in dax function

[Sudip_J]

Hi Teams,
I have orders table in which category,subcategory and sales are there .
I want to create a calculated table to get output like in the following images - 

Guys please help to get this dax function output .

Thanks and Regards
Sudeep Kumar

[Elena_Kalina · Accepted solution]

Hi, @Sudip_J 

If I understand you correctly, you want to create a table that calculates the sum, grouped first by category and then by sub-category, then determine the rank for these values, and finally sort the table within each category by the rank value. I suggest you create the table using the following formula.

SortedTable =

   ADDCOLUMNS(

        SUMMARIZE(

            'Superstore',

            [Category],

            [Sub-Category],

            "Total Sales", SUM('Superstore'[Sales])

        ),

        "Rank", RANKX(

            FILTER(

                SUMMARIZE('Superstore', [Category], [Sub-Category]),

                [Category] = EARLIER([Category])

            ),

            CALCULATE(SUM('Superstore'[Sales])),

            ,

            DESC

        )

    )

As for sorting, you can do it the simple way: add all columns to your table, then click on the category column, and while holding SHIFT, click on the rank column.

The result you should get

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Sudip_J]

[v-veshwara-msft]

Hi @Sudip_J ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @Sudip_J ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @Sudip_J ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you

[v-veshwara-msft · Accepted solution]

Hi @Sudip_J ,

Thanks for your question! I reproduced the scenario using sample data similar to your Orders table with Category, SubCategory, and Sales.

Sample Data used:

Here’s the calculated table DAX I used to rank total sales partitioned by category dynamically, including a dynamic sorting column (SortIndex) to get the ranks ordered correctly:

RankedSalesTable = 
VAR SummaryTable =
    ADDCOLUMNS(
        SUMMARIZE(Orders, Orders[Category], Orders[SubCategory]),
        "TotalSales", CALCULATE(SUM(Orders[Sales]))
    )
VAR CategoryList =
    ADDCOLUMNS(
        VALUES(Orders[Category]),
        "CategoryIndex", RANKX(ALL(Orders[Category]), [Category], , ASC, DENSE)
    )
RETURN
    ADDCOLUMNS(
        SummaryTable,
        "CategoryRank",
        VAR CurrentCategory = [Category]
        RETURN
            RANKX(
                FILTER(SummaryTable, [Category] = CurrentCategory),
                [TotalSales],
                ,
                DESC,
                DENSE
            ),
        "SortIndex",
        VAR CurrentCategory = [Category]
        VAR CategoryIndex =
            MAXX(
                FILTER(CategoryList, [Category] = CurrentCategory),
                [CategoryIndex]
            )
        VAR RankWithinCategory =
            RANKX(
                FILTER(SummaryTable, [Category] = CurrentCategory),
                [TotalSales],
                ,
                DESC,
                DENSE
            )
        RETURN
            CategoryIndex * 100 + RankWithinCategory
    )

Calculated Table Output (with SortIndex):

The SortIndex helps you sort the table first by category, then by rank, which you can use to sort your table visual in Power BI.

After sorting, you can hide the SortIndex column in your report to keep it clean.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[pankajnamekar25]

Hello @Sudip_J 

Try this measure 

Rank by Category = 

RANKX(

    FILTER(

        ALL('Orders'),

        'Orders'[Category] = MAX('Orders'[Category])

    ),

    CALCULATE(SUM('Orders'[Total_Sales])),

    ,

    DESC,

    DENSE

)

[Ashish_Excel]

Hi,

Why do you want to create a calculated table?  Why not write measures?

[Elena_Kalina · Accepted solution]

Hi, @Sudip_J 

If I understand you correctly, you want to create a table that calculates the sum, grouped first by category and then by sub-category, then determine the rank for these values, and finally sort the table within each category by the rank value. I suggest you create the table using the following formula.

SortedTable =

   ADDCOLUMNS(

        SUMMARIZE(

            'Superstore',

            [Category],

            [Sub-Category],

            "Total Sales", SUM('Superstore'[Sales])

        ),

        "Rank", RANKX(

            FILTER(

                SUMMARIZE('Superstore', [Category], [Sub-Category]),

                [Category] = EARLIER([Category])

            ),

            CALCULATE(SUM('Superstore'[Sales])),

            ,

            DESC

        )

    )

As for sorting, you can do it the simple way: add all columns to your table, then click on the category column, and while holding SHIFT, click on the rank column.

The result you should get

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Sudip_J]

Hi Elena ,
Thanks for providing your solution. I aprreciate your effort and time .

[johnt75]

If you have a measure [Total Sales] then you can use

Rank =
RANK (
    Orders,
    ORDERBY ( [Total Sales], DESC ),
    PARTITIONBY ( Orders[Category], Orders[Sub_category] )
)

[Sudip_J]

@ johnt75
 I got correct answer which works fine . I am attaching the cocrrect answer with matric image please have a look- 

[Sudip_J]

Hi john,
Thanks for trying to provide the solution and I valued your effort .But it is not showing as I want .
I am going to attached your solution with matrix ,can you please check it .