Ranking by Region/Product Category/SKU

[Anonymous]

Hello,

I want to write a dax query, something using Rankx and windows function to get a table that will give me rank for each SKU'sales  based on the category and region filters.

I have 4 tables linked to each(Sales table linked to Region by location code, Product by SKU and used to calculate the total sales.

I want to calculate the rank of each SKU by Product and Region and display then in ascending order.

Example:

Can anyone help me?

Thanks,

Ekta

@Zubair_Muhammad I saw that you solved a similar problem but it did not work for me. Could you tell me how to go about this one? Would be a great help!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have  a try.

Create a measure.

Rank by Product and Region =
VAR CurrentSKU = Sales[SKU]
VAR CurrentProduct =
    RELATED ( Product[Product] )
VAR CurrentRegion =
    RELATED ( Region[Region] )
RETURN
    RANKX (
        FILTER (
            ALL ( Sales ),
            Sales[SKU] <> BLANK ()
                && RELATED ( Product[Product] ) = CurrentProduct
                && RELATED ( Region[Region] ) = CurrentRegion
        ),
        CALCULATE ( SUM ( Sales[Sales] ) )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hello, could you tell me which column are we referring to as Sales[Prodcut]?

Sales[Product] = SELECTEDVALUE(RankTable[Product]) && Sales[Region]

The sales table has SKU is linked to the product table with this SKU.. 

[Anonymous]

Got it, but it did not work, it's displaying 1 everywhere

[123abc]

To calculate the rank of each SKU's sales based on category and region filters in Power BI using DAX, you can use the RANKX function and FILTER to create the desired table. Here's a step-by-step guide:

Assuming you have the following tables:

1. Sales
2. Region
3. Product
4. SKU

And you want to calculate SKU rank by Product and Region, follow these steps:

1. Create a calculated table to define the combinations of Product and Region you want to rank by. You can do this in the 'Modeling' tab by going to 'New Table' and entering the DAX formula:

RankTable = SUMMARIZE(CROSSJOIN(Product, Region), Product[Product], Region[Region])

This will create a table with all possible combinations of Product and Region.

1. Next, create a measure to calculate the rank. In this measure, you will use RANKX to rank SKU sales within the filtered context of Product and Region. Go to 'Modeling' > 'New Measure' and enter the DAX formula:

SKU Rank =
RANKX(
FILTER(
ALL(Sales),
Sales[Product] = SELECTEDVALUE(RankTable[Product]) && Sales[Region] = SELECTEDVALUE(RankTable[Region])
),
[Total Sales]
)

In this measure:

• FILTER is used to apply the filter context of the selected Product and Region from the RankTable.
• RANKX ranks the SKU sales within the filtered context.
• [Total Sales] should be replaced with the actual name of your sales column.

1. Finally, create a table visualization and add the SKU, Product, Region, and SKU Rank to the table. Apply your desired filters for Product and Region, and the SKU Rank will be calculated based on these filters.

Make sure that you have proper relationships set up between your tables, and the column names in the DAX formulas match your actual column names.

By following these steps, you should be able to calculate the rank of each SKU's sales based on the selected category (Product) and region filters in ascending order.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.