TopN DAX limitation

[Radsanush]

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.

Product	Category	Sales	AvgProfit
A	c1	125.45	-10.00
A	c2	234.76	-2.25
A	c3	240.01	3.50
A	c4	263.80	8.90
A	c5	150.00	11.78
B	c1	145.00	-74.65
B	c2	250.00	-56.90
B	c3	175.80	-32.45
B	c4	382.56	-24.90
B	c5	746.90	-2.00
B	c6	200.50	5.50
D	c1	575.00	18.95
D	c2	450.75	30.00
E	c1	130.98	-3.35
E	c2	610.32	14.45
E	c3	185.45	25.99
E	c4	490.00	45.35
E	c5	1256.90	75.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?

Product	Category	Sales	AvgProfit	Expected Leastprofitcat	Actual Leastprofitcat
A	c1	125.45	-10.00	125.45	125.45
A	c2	234.76	-2.25	234.76	234.76
A	c3	240.01	3.50	240.01
A	c4	263.80	8.90
A	c5	150.00	11.78
B	c1	145.00	-74.65	145.00	145.00
B	c2	250.00	-56.90	250.00	250.00
B	c3	175.80	-32.45	175.80	175.80
B	c4	382.56	-24.90
B	c5	746.90	-2.00
B	c6	200.50	5.50
D	c1	575.00	18.95	575.00
D	c2	450.75	30.00	450.75
E	c1	130.98	-3.35	130.98	130.98
E	c2	610.32	14.45	610.32	610.32
E	c3	185.45	25.99	185.45
E	c4	490.00	45.35
E	c5	1256.90	75.00

Thanks,

Radhika.R

[v-easonf-msft]

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 · Accepted solution]

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:

https://community.powerbi.com/t5/Desktop/RANKX-positive-to-Negative/m-p/458734

Thanks,

Radhika.R

[Whitewater100]

Hi:

I added a date table to help with Time Intel. This looks like it's working.

The sales amount, even for the 3 least profitable categories will always be positive in this case.

You can swop in/out prdouct or cat in the TOPN measure.

Here is the link:

https://drive.google.com/file/d/1MVedoy-92VjIEjtg95_syXfx_YxPwWoF/view?usp=sharing 

[Radsanush]

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]

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