topic Re: TopN DAX measure for filter table _ topN doesn't work properly in DAX Commands and Tips

TopN DAX measure for filter table _ topN doesn't work properly

Mai_Ng2587 — Tue, 26 Sep 2023 09:51:46 GMT

Dear Power Bi Community,

Can anyone please help me solve an issue with my Dax measures?

The idea is that I want to retrieve top 3 negative value in a dataset, and I use this DAX measure below

Var top3 = TOPN(3,

FILTER(SUMMARIZE('Sale table','Date'[Quarter],'Date'[Year]),[Sales_QTD] < 0),

[Sales_QTD],DESC)

Var top3_sum = SUMX(top3,[Sales_QTD])

Var total_sum_negative = SUMX(FILTER(ALL(New_Function[Functions]),[Sales_QTD] < 0), [Sales_QTD])

Var Othervalue = total_sum_negative - top3_sum

Var Otherselected = SELECTEDVALUE(New_Function[Functions]) = "Other"

Return

If(HASONEVALUE(New_Function[Functions]),

if(not Otherselected,

CALCULATE([Sales_QTD],KEEPFILTERS(top3)),

Othervalue),

total_sum_negative)

However, when I drop this measure to the table, the topN function is totally not working. The result is that I got all negative value in the table in 1 quarter and other category equals total negative value. Below is the result I got, which is not my intention. What I want to retrieve in the matrix is A, C & D and Other will be the total of rest (including D, E, F, S, L, G, R)

So, please if anyone has solution, I would be very thankful.

Update:

Thank you both TomasAndersson ThxAlot so much for your solution. It worked now on my real dataset!!

Re: TopN DAX measure for filter table _ topN doesn't work properly

TomasAndersson — Sat, 23 Sep 2023 08:01:02 GMT

Hi!

I modified your top10 variable a bit. You need to select all "functions" because otherwise each function is just compared to itself, which will always result in it being "top 3".

Top 3new = Var top10 = TOPN(3, ALLSELECTED(New_Function[Functions]), [Sales_QTD], ASC) Var top10_sum = SUMX(top10,[Sales_QTD]) Var total_sum_negative = SUMX(FILTER(ALL(New_Function[Functions]),[Sales_QTD] < 0), [Sales_QTD]) Var Othervalue = total_sum_negative - top10_sum Var Otherselected = SELECTEDVALUE(New_Function[Functions]) = "Other" Return If(HASONEVALUE(New_Function[Functions]), if(not Otherselected, CALCULATE([Sales_QTD],KEEPFILTERS(top10)), Othervalue), total_sum_negative)

This solution gives the top 3 largest (negative) values. D and E have an equal size so if you want to only D to show for example you would need another criteria (maybe you can wrap this in a Top N based on function name or something like that). I think it works more as you want anyway.

This is a great source for working with Top N and Others so I recommend you look into it for more resources. https://goodly.co.in/top-n-and-others-power-bi/

Good luck!

Re: TopN DAX measure for filter table _ topN doesn't work properly

ThxAlot — Sat, 23 Sep 2023 08:19:47 GMT

Example_TopN.pbix

Re: TopN DAX measure for filter table _ topN doesn't work properly

Mai_Ng2587 — Sat, 23 Sep 2023 09:42:16 GMT

Hi there,

Thank you so much for the solution but this solution is missing the calculation of Other (which should be the sum of other functions but not the sum of top 3 functions). How should I calculate Other in this way?

Re: TopN DAX measure for filter table _ topN doesn't work properly

ThxAlot — Sat, 23 Sep 2023 10:10:47 GMT

Row of Other is already there; it sums up (-9, -8, -8, -6, -4, -1).

Re: TopN DAX measure for filter table _ topN doesn't work properly

Mai_Ng2587 — Sat, 23 Sep 2023 10:43:03 GMT

Hi Tomas,

Thank you for helping me modify the Dax measure. Do you know why I can't use the filter table [FILTER(SUMMARIZE('Sale table','Date'[Quarter],'Date'[Year]),[Sales_QTD] < 0)] in my topN dax formular? In my real data, unfortunately, if I use Allselected instead of filter & summarize, I somehow got the whole value through all year instead of being filtered by 'Date'[Quarter],'Date'[Year].

Re: TopN DAX measure for filter table _ topN doesn't work properly

TomasAndersson — Sat, 23 Sep 2023 11:31:25 GMT

I won't be near a computer for a few hours but can look into it later today.

I didn't consider the time period but maybe you can have both? What happens if you add that filter in addition to ALLSELECTED?

Re: TopN DAX measure for filter table _ topN doesn't work properly

Mai_Ng2587 — Sat, 23 Sep 2023 20:17:07 GMT

I tried again with your solution and found the error in my real dataset that make this solution doesn't work. Thank you so much. Now it is solved!

Re: TopN DAX measure for filter table _ topN doesn't work properly

TomasAndersson — Sat, 23 Sep 2023 21:58:53 GMT

Thank you for fixing sample files. Helps a lot.

I spent too much time trying to get the DAX to work, but it seems it was mostly a modelling issue. I just changed the relationship between New_Function and Dim_Tab to a one-to-many instead:

This way, the same measure from earlier works.

Don't ask me exactly why. Maybe the lack of Dim_Tab filtered out "Other" from New_Function as well so that it wasn't included in the filter and Otherselected never returned True? Not sure. It looks better now at least. Only negatives are counted, but I think that was what you were looking for? Not difficult to change the filter condition otherwise.

I tested adding an extra Top N to a new measure to only return three values, even in a tie. In case that's something you want to use.

Top 3new no ties = Var top10 = TOPN( //One top N to only select three values 3, TOPN( //if this top n that calculates sales returns more than three functions 3, FILTER(ALL(New_Function[Function]),[Sales_QTD] < 0), //You could use "<> 0" if you want to allow positive values as well [Sales_QTD], ASC ), New_Function[Function], //right now the tie breaker Top N is dependent on the function name ASC ) Var top10_sum = SUMX(top10,[Sales_QTD]) Var total_sum_negative = SUMX(FILTER(ALL(New_Function[Function]),[Sales_QTD] < 0), [Sales_QTD]) Var Othervalue = total_sum_negative - top10_sum Var Otherselected = SELECTEDVALUE(New_Function[Function]) = "Other" Return If(HASONEVALUE(New_Function[Function]), if(not Otherselected, CALCULATE('Sale table'[Sales_QTD],KEEPFILTERS(top10)), Othervalue), total_sum_negative)

Re: TopN DAX measure for filter table _ topN doesn't work properly

Mai_Ng2587 — Sun, 24 Sep 2023 10:37:48 GMT

Thank you for your help 🙌. I fixed the modelling issue earlier same as your suggestion and got the correct value in my measure.

Also, your new tesing measure with the nested topN is very helpful as now I know that I can use topN with filter as well. Many thanks!!!!!

Re: TopN DAX measure for filter table _ topN doesn't work properly

TomasAndersson — Sun, 24 Sep 2023 10:40:51 GMT

Great, glad it worked!