Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Mai_Ng2587
Regular Visitor

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

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)
 
Mai_Ng2587_0-1695419448490.png

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
TomasAndersson
Solution Sage
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.

TomasAndersson_0-1695455814695.png

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!

View solution in original post

10 REPLIES 10
TomasAndersson
Solution Sage
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?

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! 

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:

TomasAndersson_0-1695505901606.png

 

This way, the same measure from earlier works.

TomasAndersson_1-1695505979015.png

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)

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

Great, glad it worked!

ThxAlot
Super User
Super User

Example_TopN.pbix

 

ThxAlot_0-1695457210139.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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?

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)


)



TomasAndersson
Solution Sage
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.

TomasAndersson_0-1695455814695.png

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!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.