Top N + Others but total is not adding up

[Irshadn03]

I was trying to do "Top N + others", tried many options from different community and finaly only one solution worked for me from below link,
https://www.goodly.co.in/top-n-and-others-power-bi/

Now problem is total is not considering "Others" - it should be 29M instead of 4M.

Can anyone suggest how to fix this "Total"..

Here is the DAX used ,

Top N Sum Sales =

VAR TopNSelected = SELECTEDVALUE('TopN Selection'[Value])

VAR TopProdTable =

TOPN(

TopNSelected,

ALLSELECTED('Pseudo Prod Table'),

[Total Sales]

)

VAR TopProdSales =

CALCULATE(

[Total Sales],

KEEPFILTERS( TopProdTable )

)

VAR OtherSales =

CALCULATE(

[Total Sales],

ALLSELECTED('Pseudo Prod Table')

) -

CALCULATE(

[Total Sales],

TopProdTable

)

VAR CurrentProd = SELECTEDVALUE('Pseudo Prod Table'[Product])

RETURN

IF(

CurrentProd <> "Others",

TopProdSales,

OtherSales

)

attached two solutions PBIX files I used here,
https://we.tl/t-xIzgNWAzme

Thanks in advacne 

[AlexisOlson · Accepted solution]

This is because your CurrentProd is blank for the Total case (since multiple Products are in scope).

Try updating the RETURN part of your DAX to this:

RETURN
    IF(
        ISBLANK ( CurrentProd ),
        TopProdSales + OtherSales,
        IF (
            CurrentProd <> "Others",
            TopProdSales,
            OtherSales
        )
    )

[AlexisOlson · Accepted solution]

This is because your CurrentProd is blank for the Total case (since multiple Products are in scope).

Try updating the RETURN part of your DAX to this:

RETURN
    IF(
        ISBLANK ( CurrentProd ),
        TopProdSales + OtherSales,
        IF (
            CurrentProd <> "Others",
            TopProdSales,
            OtherSales
        )
    )

[Irshadn03]

Thank you so much  @AlexisOlson  , it is adding up now.
Any option to do the same for "Bottom" N instead of "Top" for the same table - couldn't find a solution

[AlexisOlson]

You can use the opposite sorting in the TOPN function. That is, use ASC rather than the default DESC.

[Irshadn03]

Tried it but getting only "Others" total.

Top N Sum Sales v2 = 
VAR TopNSelected = SELECTEDVALUE('TopN Selection'[Value])
VAR TopProdTable = 
    TOPN(
        TopNSelected,
        ALLSELECTED('Pseudo Prod Table'),
        [Total Sales],ASC
    )    
VAR TopProdSales =
    CALCULATE(
        [Total Sales],
        KEEPFILTERS( TopProdTable )
    )
VAR OtherSales = 
    CALCULATE(
        [Total Sales],
        ALLSELECTED('Pseudo Prod Table')
    ) - 
    CALCULATE(
        [Total Sales],
        TopProdTable
    )
VAR CurrentProd = SELECTEDVALUE('Pseudo Prod Table'[Product])
RETURN
    IF(
        ISBLANK ( CurrentProd ),
        TopProdSales + OtherSales,
        IF (
            CurrentProd <> "Others",
            TopProdSales,
            OtherSales
        )
    )

here is the output 

[AlexisOlson · Accepted solution]

Hmm. Maybe the bottom products don't have any sales, so they're returning blank.