Forum Discussion

Irshadn03's avatar
Irshadn03
Frequent Visitor
4 years ago
Solved

Top N + Others but total is not adding up

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's avatar
    AlexisOlson
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

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

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Irshadn03
      Frequent Visitor
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

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