Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
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!!
Solved! Go to 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!
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:
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)
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!
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) ) |
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!
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].
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |