topic Re: Ranking by Region/Product Category/SKU in DAX Commands and Tips

Ranking by Region/Product Category/SKU

Anonymous — Thu, 12 Oct 2023 10:27:11 GMT

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!

Re: Ranking by Region/Product Category/SKU

123abc — Thu, 12 Oct 2023 11:30:05 GMT

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:

Sales
Region
Product
SKU

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

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.

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.

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.

Re: Ranking by Region/Product Category/SKU

Anonymous — Thu, 12 Oct 2023 11:56:38 GMT

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..

Re: Ranking by Region/Product Category/SKU

Anonymous — Thu, 12 Oct 2023 12:48:29 GMT

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

Re: Ranking by Region/Product Category/SKU

Anonymous — Mon, 16 Oct 2023 02:42:33 GMT

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.