SumX all negative and positive values of my Top5?

[superBro22]

Hello,

I'm using this measure : SUMX ( Top5Cust, [@SalesofALL] ) in order to sum all sales values of the Top5 product but this formula only works when the values are positive, It doesn't work  for  positive  and/or negative values.

Can someone help me please?

Thanks!

[PaulDBrown]

I take it this is related to this other thread (in which I just posted a possible solution btw)
https://community.powerbi.com/t5/Desktop/Top-N-others-and-null-values/m-p/2819686#M974780 

To ba able to help with your request in this thread, we need to understand the structure of the data. So a non-confidential sample is needed (you can copy and paste from Excel for example), or you can upload a non-confidential PBIX file to OneDrive, DropBox, Google Drive, iCloud or WeTransfer 

[superBro22]

My matrix looks like this: The OTHER value is the rest of sales value of the TOPN and  it calculated by (Grand Total - sum of  all TopN values). This is the result I'm expecting:

The issue is, when some value is negatif, the SUMX of TopN doesn't work. I got the following result: 

The SUMX Does not consider the negative value of D

[PaulDBrown · Accepted solution]

If you follow the method I posted in the other thread 
https://community.powerbi.com/t5/Desktop/Top-N-others-and-null-values/td-p/2817483 

(note, I had to edit one of the measures to get the correct value for "Other")

it should work with negative numbers:

Sample PBIX file

[superBro22]

Yes it's working. Thanks so much for your help

[PaulDBrown]

Please provide sample non-confidential data or a link to a PBIX file in a cloud service

[superBro22]

I don't find the way to attach a PBIX from here.

This is my sale measure for TopN:

Sales TopN =

VAR SalesofALL = CALCULATE('Main measures'[Sales],REMOVEFILTERS('Product Name'[Products Description]))

RETURN

    IF(

        ISINSCOPE('Product Name'[Products Description]),

        VAR ProductsToRank = [TopN Value]

        VAR IsOtherSelected = SELECTEDVALUE('Product Name'[Products Description]) = "OTHER"

        VAR ProductsWithSales = ADDCOLUMNS(ALLSELECTED('Product Name'[Products Description]),"@SalesofALL",'Main measures'[Sales])

        VAR Top5Cust = TOPN(ProductsToRank,ProductsWithSales,[@SalesofALL])

        VAR SalesofTop5 = SUMX(Top5Cust,[@SalesofALL])

        VAR Result = IF(IsOtherSelected,SalesofALL - SalesofTop5,'Main measures'[Sales])

        RETURN Result,

        SalesofALL

    )