Top 03 on different columns based on slicer selection

[RAHEEL]

Hi All,

I have two measure columns in a table dataset(i.e. products), 1st measure is reg_sales and 2nd is Promo_Sales,

I have another table which is being used as slicer/filter and it contains 2 values (i.e. Regular and Promo)

Now I need to diplay top 3 products with hightest reg_sales from products table when slicer selection is set to "Regular"

and 

I need to display top 3 products with highest promo_sales from products table when slicer selection is set to "Promo"

Thanks & Regards,

Raheel

[v-yulgu-msft · Accepted solution]

Hi @RAHEEL,

From your description, [reg_sales] and [Promo_Sales] are measures , not initial columns or calculated columns, right?

In that case, please create measures like below:

Rank for reg = RANKX(ALL('Product'),[reg_sales],,DESC,Dense)

Rank for promo = RANKX(ALL('Product'),[Promo_Sales],,DESC,Dense)

Rank = IF(LASTNONBLANK('Slicer Table'[Regular/Promo],1)="Promo",[Rank for promo],[Rank for reg]) 

Then, add measure [Rank] to visual level filters, set its value to 'is less than or equal to' 3.

Best regards,

Yuliana Gu

[SteveCampbell]

Hello,

I would reccomend unpivoting your data, so that each product has a row for each Price. To do this, open the query editor. Highlight both columns of reg_sales and Promo_Sale., On the "Transform" tab, click the dropdown for unpivot columns and select "Unpivot only Selected Columns".

This will give you two new columns of Attribute and Value (You can rename). Use Attribute as your slicer (or you can join this to your other table if you need other info in this table).

You can add a table visual and display your Product ID/Name and Value. On the Visual Level Filters, select Product ID/Name and change filter type to Top N. Change the input to 3 and drag in Value in the By value field.