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 ...
  • 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.

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.

      • Irshadn03's avatar
        Irshadn03
        Frequent Visitor
        4 years ago

        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