Forum Discussion

mphillenga's avatar
mphillenga
Icon for Helper I rankHelper I
4 years ago

Ranking TopN negative values

Hello,

 

I've created a chart that presents the top 5 and 'others' products based on a sales metric.

Some of the sales values of products are negative. With the DAX that I'm currently using, the negative values are not included in the top 5. I'm thinking TopN is not the right DAX to use.

 

To clarify, if I have a top 5 products as list below. Only the positive values are included in the top 5, the negative ones are ignored.

 

 

 

I used the instructions from this website.

The code I use is:

 

Top Products =
IF (
ISFILTERED ( 'Products Ranking'[Ranking Products] ),
VAR NumOfProducts = 9
VAR RankingName =
SELECTEDVALUE ( 'Products Ranking'[Ranking Products] )
VAR TopProducts =
TOPN ( NumOfProducts, ALLSELECTED ( 'Products Ranking' ), [Sum_Sales] )
RETURN
IF (
RankingName = "Other",
VAR TopAmount =
CALCULATE ( [Sum_Sales], TopProducts )
VAR AllAmount =
CALCULATE ( [Sum_Sales], ALLSELECTED ( 'Products Ranking' ) )
VAR OtherAmt = AllAmount - TopAmount
RETURN
OtherAmt
,
CALCULATE ( [Sum_Sales], KEEPFILTERS ( TopProducts ) )
)
,
[Sum_Sales]
)

 

 

Does anyone have an idea what DAX to use to include negative values in the chart?

 

Thanks a lot

Need Help
Tips and Tricks

1 Reply

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

    mphillenga , Create a measure like example

     

    sumx(filter(values(Table[Product]), [Sales] >0 ), [Sales])

     

     

    Create rank on this