You saved my day! Scenario was: how many distinct donors NGO needed to cover >50% of its annual budget in each year. Very useful and instructive video. Thank you.
Alberto and Marco - the issue is not so much whether I am interested in Top 10% or not (I am!). The real point is how fantastically detailed your videos are. Regardless of the topic, I just land up learning so much on so many different fronts. If you make a video on fertilizing cats using DAX, I will still watch it!
There are already way more than 10 votes to prove Marco wrong but because you are asking so nicely, I am giving the thumb up :) . Another great video! I love this "unplugged" series, Please keep it up, no matter what Marco says... Thank you!
This perfectly describes a problem I’m working on, I can’t wait to try it in DAX studio myself. The cherry on top would be adding an “Others” bucket to capture the remaining sales in a single row which will also allow the total to display properly in the filter context.
Alberto, you are the best :) I have been looking for this example from last two months. Didn't happen to find it anywhere. I was searching for TopN by Percentage. So I guess if you add this to your description you will get more thumbs up. Well My requirment was to analyse PO by PO purchase price variance but only for products where total cost was within 80% of total spend by Brand. You solved the issue very nicely. Bravo. Very well said @Miklos Messi and Ronaldo playing for the same team.
Thank you Alberto, these videos are very helpful to me in learning to apply concepts in more practical scenarios. Calculating filters and using in calculation groups was particularly useful.
Amazing video, I wondered if the running total calculation would work in the same way if there were duplicated values in the @amt column? I.e. Wouldn't the filter function return a table that include the duplicates further down in the original table?
It's really interesting to compute the top percent. I just like to know whether it possible to replace the static value of percent with parameter such that end user can place the value in the parameter ?
Couldn't you also use a What-If analysis for whatever % your user will select, then use SELECTEDVALUE in the measure to grab that percent? That way you don't have to keep repeating the calculation groups, can simply adjust the slider?
You are a Genius. Add me in the list as well XD. Is there anyway that I can use the DAX from tabular editor directly on Power BI as a measure, column or even a new table ???
How can I convert the temporary [#AmtRT%] into a measure I can use in Power BI? I have the Top% and Calculation groups working perfectly -- exactly what I needed -- and now I see so much more that I can do! Thank you, Alberto!
@SQLBI Is there a way to report the row that totals (or surpasses) each percent grouping, instead of stopping at the last row (product, customer, whatever)? I am reporting on $ received per donor. Total donations for the year are $1.4M, so the top 10% would be 140,000. The top two donors gave $128,600, or 9.18% (which isn't quite 10%). I'd like to include the next donor ($40,000) to show how many donors it took to truly reach (or exceed) 10% of total donations. The total of the top 3 donors is $168,600, or 12.04%.
You can read this article about auto-exists: www.sqlbi.com/articles/understanding-dax-auto-exist/ Certain concepts are deep in DAX filter context and you should reference the Mastering DAX video course for that: www.sqlbi.com/p/mastering-dax-video-course/
Can you please also post the .pbix files for the unplugged series as well? I love to practice different approaches and solutions on your files and I find that this reinforces concepts so much better when I try and fail, try and fail. Thanks
We usually provide sample files in related articles on sqlbi.com - we do not have a proper space for the unplugged series, which normally doesn't have sample files. We'll look into it to see how to solve the issue. Thanks for the feedback!
@@SQLBI Thanks a million for remembering my request! I have downloaded the file and I look forward to working out all kinds of scenarios and mapping the results against the lesson!
nice video, one question, if i wanted to do it more generic (so it could work with any measure and not only for sales) would it be enoough to replace the [Sales Amount] for SELECTEDMEASURE () in your ProdsAndSales variable ?
Yes, of course. However, consider that often you want to see different measures computed with the top X% over a specific measure. The calculation item shows in this example allows you to do that.
When you move this formula into Power BI, your very last step is to "RETURN" Calculate(SelectedMeasure(), "KEEP FILTERS(TopPercPRoducts)"...... I thought you would just be able to put "TopPercProducts" and it would work but it doesn't. Why do you have to rap it in Keep filters?
❤❤❤ thank you
You saved my day! Scenario was: how many distinct donors NGO needed to cover >50% of its annual budget in each year. Very useful and instructive video. Thank you.
legend, my hat off to your, SIR!
Top percent is used in retail analysis all the time. Very very useful function! Thanks a bunch.
Alberto and Marco in one sentence: Imagine Messi and Ronaldo playing the same team.
Alberto and Marco - the issue is not so much whether I am interested in Top 10% or not (I am!). The real point is how fantastically detailed your videos are. Regardless of the topic, I just land up learning so much on so many different fronts. If you make a video on fertilizing cats using DAX, I will still watch it!
There are already way more than 10 votes to prove Marco wrong but because you are asking so nicely, I am giving the thumb up :) . Another great video! I love this "unplugged" series, Please keep it up, no matter what Marco says... Thank you!
In reality, I'm glad to lose this bet!
@@marcorusso7472 the contrary would have surprised me... ;)
@@marcorusso7472 😊p
This perfectly describes a problem I’m working on, I can’t wait to try it in DAX studio myself. The cherry on top would be adding an “Others” bucket to capture the remaining sales in a single row which will also allow the total to display properly in the filter context.
Take a look at this: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/
Alberto, you are the best :) I have been looking for this example from last two months. Didn't happen to find it anywhere. I was searching for TopN by Percentage. So I guess if you add this to your description you will get more thumbs up. Well My requirment was to analyse PO by PO purchase price variance but only for products where total cost was within 80% of total spend by Brand. You solved the issue very nicely. Bravo. Very well said @Miklos Messi and Ronaldo playing for the same team.
Doesn’t matter Alberto or Marco win the argument, as an audience, we gain useful knowledge.
Thank you Alberto, these videos are very helpful to me in learning to apply concepts in more practical scenarios. Calculating filters and using in calculation groups was particularly useful.
Thank you very much for this video. It gives me insight on how to create a measure for how many SKU contributing X% of sales. Super! :)
Amazing video, I wondered if the running total calculation would work in the same way if there were duplicated values in the @amt column? I.e. Wouldn't the filter function return a table that include the duplicates further down in the original table?
not sure if I'll ever use it, but definately worth to watch! thank you!!
Thank you Alberto for another amazing video.
absolutely great one Alberto :)
Thanks ! It's very helpfull, this point is very often requested !
I don’t just like it.. I love it 🥰
Brilliant!!!!
Awesome !!!!
I was the 10th like :) you proved your point
Brilliant as always . What about a video on Income statement/profit and loss statement?
It's really interesting to compute the top percent. I just like to know whether it possible to replace the static value of percent with parameter such that end user can place the value in the parameter ?
Yes, you can use SELECTEDVALUE for that!
5 likes in 10 minutes 😂, it seems you will be right... very interesting topic and nice example to dig more into Dax... thanks a lot
Couldn't you also use a What-If analysis for whatever % your user will select, then use SELECTEDVALUE in the measure to grab that percent? That way you don't have to keep repeating the calculation groups, can simply adjust the slider?
That was masterful
Excellent video
I've Upvoted this, and a quick shout out to Maco .......no offence meant😀
You are a Genius. Add me in the list as well XD.
Is there anyway that I can use the DAX from tabular editor directly on Power BI as a measure, column or even a new table ???
I have had this exact question in pivot which I was not able to resolve
Респект, ну ты зверь!!!
Thanks!
How can I convert the temporary [#AmtRT%] into a measure I can use in Power BI?
I have the Top% and Calculation groups working perfectly -- exactly what I needed -- and now I see so much more that I can do! Thank you, Alberto!
@SQLBI Is there a way to report the row that totals (or surpasses) each percent grouping, instead of stopping at the last row (product, customer, whatever)?
I am reporting on $ received per donor. Total donations for the year are $1.4M, so the top 10% would be 140,000. The top two donors gave $128,600, or 9.18% (which isn't quite 10%). I'd like to include the next donor ($40,000) to show how many donors it took to truly reach (or exceed) 10% of total donations. The total of the top 3 donors is $168,600, or 12.04%.
Amazing 👏
Alberto and Marco in one sentence: Imagine Pele and Maradona playing the same team. rsrsrsrs
Hello Alberto & Marco,
Can you make a video on Dax autoexist.Need to understand the underlying concept
You can read this article about auto-exists: www.sqlbi.com/articles/understanding-dax-auto-exist/
Certain concepts are deep in DAX filter context and you should reference the Mastering DAX video course for that:
www.sqlbi.com/p/mastering-dax-video-course/
Can you please also post the .pbix files for the unplugged series as well? I love to practice different approaches and solutions on your files and I find that this reinforces concepts so much better when I try and fail, try and fail. Thanks
We usually provide sample files in related articles on sqlbi.com - we do not have a proper space for the unplugged series, which normally doesn't have sample files. We'll look into it to see how to solve the issue. Thanks for the feedback!
@@SQLBI Many thanks - it would be great to have the unplugged series files, as and when they are available
Download sample file from: www.sqlbi.com/tv/toppercent-in-dax-unplugged-8/
Thank you so very much. It’s very kind of you to remember this!!!
@@SQLBI Thanks a million for remembering my request! I have downloaded the file and I look forward to working out all kinds of scenarios and mapping the results against the lesson!
nice video, one question, if i wanted to do it more generic (so it could work with any measure and not only for sales) would it be enoough to replace the [Sales Amount] for SELECTEDMEASURE () in your ProdsAndSales variable ?
Yes, of course. However, consider that often you want to see different measures computed with the top X% over a specific measure. The calculation item shows in this example allows you to do that.
How come the result of [@Amt] is formatted in dax studio?
DAX Studio has an option to automatically format query results.
HI Alberto, why you call Tabular Editor DAX Studio 3? I checked Tabular Studio is at V2.14.1. Thank you for the topic.
Your right, it is Tabular Editor 3 (currently in preview, should be out by end of March 2021), whereas DAX Studio is still at 2.x.
@SQLBI I installed the current version of Tabular Editor and it has Calculation Groups and all is good. Thanks so much for this video!
You refer to Tabular Editor 3 several times as Dax Studio 3 around the 14:25 :D
Yes, confusion with the new version!
Alberto next time place a bit with Marco
Master Alberto I'm your padawan 🌝
You've just got the 11th like from me. Sorry Marco!
No problems! :)
When you move this formula into Power BI, your very last step is to "RETURN" Calculate(SelectedMeasure(), "KEEP FILTERS(TopPercPRoducts)"...... I thought you would just be able to put "TopPercProducts" and it would work but it doesn't. Why do you have to rap it in Keep filters?
See this: www.sqlbi.com/articles/using-keepfilters-in-dax/
Like only for proving marco is wrong😂😂
Don't worry! :)
I was the 11th!
Sorry for Marco!!
No problems!
421 ;)
Sound si not good
Marco, you've lost, buddy
I just wish it was done in a regular power bi itself not in a dax studio. It complicates complicated problems even more. Thank You anyway.