cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

Dear Power Bi Community,

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!!

1 ACCEPTED SOLUTION
Solution Sage

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!

10 REPLIES 10
Solution Sage

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?

Regular Visitor

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!

Solution Sage

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)``````
Regular Visitor

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!!!!!

Solution Sage

Super User

Example_TopN.pbix

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Regular Visitor

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?

Super User

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Solution Sage

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!

Regular Visitor

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].

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.