cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Irshadn03
Frequent Visitor

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

Irshadn03_0-1637993627609.png

 

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 

 

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

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

 

View solution in original post

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

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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

 

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

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

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 

Irshadn03_0-1638247382963.png

 


 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!