Implementing the “Top N and others” pattern using calculation groups in DAX - Unplugged #28
Вставка
- Опубліковано 12 вер 2024
- Convert a "Top N and others" measure pattern into a calculation group that can be applied to any DAX measure in a Power BI model.
Download sample file here: www.sqlbi.com/...
Read more about the "unplugged" format: www.sqlbi.com/...
#unplugged
I really need to learn calculation groups! And like pragmatic decision at 38:30 to "I'm not gonna repeat code a third time"
Really great channel! Have learned a lot through watching these, though I do have a question. I was able to use this video to get my model to work successfully, with one exception. Because the solution is essentially something like (Other Products = Total - Named Products), it doesn't work for any calculated Measures that are more than a simple Sum. For example, my model has Measures calculating percentages and a cost per unit, and they return results that don't make sense in the Others row, since the calculation should actually be figured by dividing other numbers in the Others row as opposed to doing math within the column. Any help pointing me in the right direction would be appreciated.
Absolutely the best! Thank you!!!
Hi there. Alberto, it would have been better if you had written:
if( isselectedmeasure( [ranking] ), [ranking],
)
instead of the other way round. I hope you'll agree that the above is how it should have been :)
The reasons are pretty obvious when you compare it with
if( NOT isselectedmeasure( [ranking] ),
,
[ranking]
)
This version is much harder to read and even harder when you realize how long the code in the is.
But anyway... many thanks, as always, for sharing your insights.
Hey, could you help me to adjust/write down DAX code for case where Others are already in data source, ideally I want to have Others from the source combined with Others created as result of the dynamic TopN filter. Target visual will be pie chart.
Thank you so much!!!!!
Also, in the case of transactions, the total doesn't equal the sum of the rows. You can see it on 21:20. You have a total of 2184, but when we sum up the values we get 2008+60+41+46 = 2155.
You are right. There is a know bug, MS is already working on a solution. Hard to generate the conditions to hit it... but it happened with this code. I did not notice it ended up in the video. Thanks.
Do you think it’s a bug? I think the reason is you calculate Others as All - Top3. But Top3 in case of # Trans measure is different from those of the Sales Amt measure. You can see it at 11:34 for example. You take TopN based on SELECTEDMEASURE which is # Trans but you show Top 3 by the Ranking measure which is based on the Sales Amt.
@@igorpoleshchuk735 :) You are right again. There is actually a bug, which happens in a very similar situation and that produces a very similar result, but it is not its fault here. Since I used the Ranking measure, the ranking was still being computed on the sales amount measure. The problem disappeared when everything was inside the calculation group.
I had to view the video again, I did not remember all the steps.
Excelente aula, parabéns!!
Thanks a lot for your videos.
It's really useful. Please, can you elaborate the non-additive edition.
My problem is that I am ranking my products in higher granularity - I have products, but want to rank by products group. As far as I repeat all the steps my other row ramins blank. Cant understand the reason. Please, support.
See this: www.daxpatterns.com/ranking/
@@SQLBI thanks a lot. I managed to work it round.
I have also noticed that as we apply calculation group concept to the overal calculation it stops crossfiltering other matrix within a dashboard. Is there a way to make the matrix filter other matrix?
Hi Alberto! Why couldn't we use the code of the Visible row measure directly in the Sales Amt measure in your initial solution without calculation groups? I mean, blank out the rows with the rank > 4.
You are right. It just did not come to my mind while recording.
Amazing alberto
You are amazing great explanation
Thanks to share with us amazing tricks
But i have one question alberto can you help me
I have duplicate data and i want to calculate rank like below
300---1
300--2
301---3
Please give me this dAX
In that case you just show the top 4 instead of top 3, and you can change the title to just Top Measure (e.g. Sales).
Nice video👏, can you please share the pbix file download?.. Thanks
It's here: www.sqlbi.com/tv/implementing-the-top-n-and-others-pattern-using-calculation-groups-in-dax-unplugged-28/
We've also added the link in the video description.
@@SQLBI thanks
Hi There, hope you're doing well and safe... How can use TopN 5 products and select top 5 countries from different columns while creating stacked bar chart...
I am able to select only one criteria using TopN