topic Re: Top N + Others but total is not adding up in DAX Commands and Tips

Top N + Others but total is not adding up

Irshadn03 — Sat, 27 Nov 2021 06:47:02 GMT

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

Re: Top N + Others but total is not adding up

AlexisOlson — Sat, 27 Nov 2021 16:34:59 GMT

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

Re: Top N + Others but total is not adding up

Irshadn03 — Sun, 28 Nov 2021 06:36:05 GMT

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

Re: Top N + Others but total is not adding up

AlexisOlson — Sun, 28 Nov 2021 17:07:57 GMT

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

Re: Top N + Others but total is not adding up

Irshadn03 — Tue, 30 Nov 2021 04:43:35 GMT

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

Re: Top N + Others but total is not adding up

AlexisOlson — Tue, 30 Nov 2021 15:59:40 GMT

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