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

[HassanAshas]

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

[tamerj1 · Accepted solution]

@HassanAshas 

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

[HassanAshas]

@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 ) )

[tamerj1]

@HassanAshas 

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], OtherProducts )