Forum Discussion

Radsanush's avatar
Radsanush
New Member
4 years ago
Solved

TopN DAX limitation

Hello everyone,

I have run in to a problem while using TopN DAX function with ASC sort order on a column with mix of positive and negative values. Can anyone please help?

Below is the table. I am trying to find the Total Sales of 3 least profitable Categories under each Product.

ProductCategorySalesAvgProfit
Ac1125.45-10.00
Ac2234.76-2.25
Ac3240.013.50
Ac4263.808.90
Ac5150.0011.78
Bc1145.00-74.65
Bc2250.00-56.90
Bc3175.80-32.45
Bc4382.56-24.90
Bc5746.90-2.00
Bc6200.505.50
Dc1575.0018.95
Dc2450.7530.00
Ec1130.98-3.35
Ec2610.3214.45
Ec3185.4525.99
Ec4490.0045.35
Ec51256.9075.00

 

This is the DAX statement I used :

Leastprofitcat = CALCULATE([Tot_sales],
TOPN
(3, ALL(Sheet1[Category]), [Avg_Profit], ASC), VALUES(Sheet1[Category]))
 
Below is the result table with what I expected the DAX statement will give and what actually I got.
Does TopN DAX statement with ASC will work only with values below Zero?
By that logic, for Product A, the 3rd row with positive avgprofit(3.50) is omitted. But I don't know why
Product E second row with positive profit(14.45) is picked up?
Can anyone please help me understand whats wrong with my statement?
ProductCategorySalesAvgProfitExpected LeastprofitcatActual Leastprofitcat
Ac1125.45-10.00125.45125.45
Ac2234.76-2.25234.76234.76
Ac3240.013.50240.01 
Ac4263.808.90  
Ac5150.0011.78  
Bc1145.00-74.65145.00145.00
Bc2250.00-56.90250.00250.00
Bc3175.80-32.45175.80175.80
Bc4382.56-24.90  
Bc5746.90-2.00  
Bc6200.505.50  
Dc1575.0018.95575.00 
Dc2450.7530.00450.75 
Ec1130.98-3.35130.98130.98
Ec2610.3214.45610.32610.32
Ec3185.4525.99185.45 
Ec4490.0045.35  
Ec51256.9075.00  

 

Thanks,

Radhika.R

  • Radsanush's avatar
    Radsanush
    4 years ago

    Hi,

    Thanks for providing this alternative solution. But I figured my original DAX statement containing ALL() and TOPN needs to be rewritten like this with Filter():

    Leastprofitcat2 = CALCULATE([Tot_sales], TOPN(4, filter(ALL(Sheet1[Category]), [Avg_profit] <> BLANK()), [Avg_Profit], ASC), VALUES(Sheet1[Category]))
    Please refer to this post:
     
    Thanks,
    Radhika.R
     

5 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Radsanush 

    If 'TopN' is not feasible, you can try 'Rankx' to filter the data to achieve the same result.

    Leastprofitcat =
VAR _rank =
    RANKX (
        FILTER ( ALL ( Sheet1 ), Sheet1[Product] = MAX ( Sheet1[Product] ) ),
        CALCULATE ( AVERAGE ( Sheet1[AvgProfit] ) ),
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( _rank <= 3, Sheet1[Tot_sales] )

    Best Regards,
    Community Support Team _ Eason

     

     

    • Radsanush's avatar
      Radsanush
      New Member
      4 years ago

      Hi,

      Thanks for providing this alternative solution. But I figured my original DAX statement containing ALL() and TOPN needs to be rewritten like this with Filter():

      Leastprofitcat2 = CALCULATE([Tot_sales], TOPN(4, filter(ALL(Sheet1[Category]), [Avg_profit] <> BLANK()), [Avg_Profit], ASC), VALUES(Sheet1[Category]))
      Please refer to this post:
       
      Thanks,
      Radhika.R
       
    • Radsanush's avatar
      Radsanush
      New Member
      4 years ago

      Hi,

      Thanks. I am not sure how adding a date table or converting the profit values to positive is helpful.

      anyway, I have found the solution and its in the previous post.

      Thanks,

      Radhika.R

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hiļ¼Œ Radsanush 

        If your problem has been solved, please accept a reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

         

        Best Regards,
        Community Support Team _ Eason