cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RAHEEL Regular Visitor
Regular Visitor

Top 03 on different columns based on slicer selection

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Top 03 on different columns based on slicer selection

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.

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2

Re: Top 03 on different columns based on slicer selection

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.

Community Support Team
Community Support Team

Re: Top 03 on different columns based on slicer selection

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.

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.