topic Re: How to display all the Products except the Top 3 Best Selling Products in a Visual in DAX Commands and Tips

How to display all the Products except the Top 3 Best Selling Products in a Visual

Anonymous — Fri, 07 Apr 2023 15:21:19 GMT

I have a visual created to display the Top 3 Products + Others with their Sale amounts.

I am trying to make a Tooltip visual where I can show all the Products that fall into "Others" Category, but I can't seem to think of any way with which I can do this.

As a template, I followed this Youtube video: https://www.youtube.com/watch?v=yGFcCbXn_g0

and did exactly what he did to display the Top 3 Products + Others. Now, I need a similar visual that shows all the Products except the Top 3.

Following DAX is used to display the only Top 3 Products + the Others

Top N SUM = VAR TOPNSelected = SELECTEDVALUE('TopN Selection'[Value]) VAR CurrentProd = SELECTEDVALUE('Pseudo Prod Table'[Product]) VAR TopProducts = TOPN( TopNSelected, ALLSELECTED('Pseudo Prod Table'[Product]), [Total Sales] ) VAR TopProdSales = CALCULATE( [Total Sales], KEEPFILTERS( TopProducts ) ) RETURN SWITCH( TRUE(), CurrentProd <> "Others", TopProdSales, CurrentProd = "Others", CALCULATE( [Total Sales], ALLSELECTED('Pseudo Prod Table'[Product]) ) - CALCULATE( [Total Sales], TopProducts ) )

I tried using different DAX functions such as EXCEPT but couldn't really come up with a solution

Can anyine help out in this?

You may download the Power BI File from here:

https://drive.google.com/file/d/16C1hoGOW2yQDrjcqXkrMKmik7VnRVhj5/view?usp=sharing

Re: How to display all the Products except the Top 3 Best Selling Products in a Visual

tamerj1 — Fri, 07 Apr 2023 16:31:33 GMT

Anonymous

Please try

Others SUM =
VAR TOPNSelected =
SELECTEDVALUE ( 'TopN Selection'[Value] )
VAR SelectedProducts =
ALLSELECTED ( 'Pseudo Prod Table'[Product] )
VAR TopProducts =
TOPN ( TopNSelected, SelectedProducts, [Total Sales] )
VAR OtherProducts =
EXCEPT ( SelectedProducts, TopProducts )
RETURN
CALCULATE ( [Total Sales], KEEPFILTERS ( OtherProducts ) )

Re: How to display all the Products except the Top 3 Best Selling Products in a Visual

Anonymous — Fri, 07 Apr 2023 23:38:14 GMT

tamerj1 That worked like a charm! Thank you so much. Also thanks a lot for writing such a clean and understandable code, I totally get what I was doing wrong with my code.

One more thing I would like to ask if you can answer. I actually have a Bar Chart in a report. This bar chart shows the sales of all the Products except top 3 (using this measure).

I am putting this report as a "Tooltip" in my main Report where I am showing Top 3 Products + Others. So what I am trying to achieve is actually show the sales of all the products except top 3 when visual is hovered.

But because hovering over the visual in main report "filters" the Bar Chart in the Tooltip report, I am getting blank in the tooltip. Is there any way I can escape the filter in the Bar Chart in any way?

I tried to change ALLSELECTED to "ALL" but it unfortunately did not work,

Others SUM = VAR TOPNSelected = SELECTEDVALUE ( 'TopN Selection'[Value] ) VAR SelectedProducts = ALL( 'Pseudo Prod Table'[Product] ) VAR TopProducts = TOPN ( TopNSelected, SelectedProducts, [Total Sales] ) VAR OtherProducts = EXCEPT ( SelectedProducts, TopProducts ) RETURN CALCULATE ( [Total Sales], KEEPFILTERS ( OtherProducts ) )

Re: How to display all the Products except the Top 3 Best Selling Products in a Visual

tamerj1 — Sat, 08 Apr 2023 07:23:03 GMT

Anonymous

Please try