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

[Mai_Ng2587]

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

[TomasAndersson]

 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?

[Mai_Ng2587]

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! 

[TomasAndersson]

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)

[Mai_Ng2587]

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

[TomasAndersson]

Great, glad it worked!

[ThxAlot]

Example_TopN.pbix

[Mai_Ng2587]

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?

[ThxAlot]

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

[TomasAndersson · Accepted solution]

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!

[Mai_Ng2587]

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