I teach a simple introduction class for an accounting product and I always recommend you guys as a resource for those looking for videos that are easy to follow. I learn new things from your channel as well. I love the short videos as it sparks ideas for things I can use to help me build dashboards for my own projects and gives me great direction for when someone asks about YoY, now I don't have to tell them to do as many DAX. So helpful!! AS ALWAYS.
Patrick. Thanks. Good stuff! Good to note that implicit measures are no longer available if you add a calculation group. I know that explicit measures are best practice, but that can be a surprise to those unaware of that behavior.
Limitation or drawback of using calculation groups is that you may be unable to apply conditional formatting by measures. For example, in Patrick's example @ 6:44 , what if you wanted to conditionally format a gradients YoY column that applies a gradient theme on most negative to positive amounts? Let me know if someone has a workaround it.
4 ปีที่แล้ว +5
I have submitted this as an idea ideas.powerbi.com/ideas/idea/?ideaid=2bbc3ffd-78f2-ea11-bf21-0003ff52cdee.
This is great! I used to manually build slicer tables and construct switch measures to replicate that kind of behaviour, but this is much more scalable.
I like it, but the lack of actual measures appearing in the calculation group will confuse users to no end. Would be great if they would just auto-generate named measures that could be dragged into visuals individually, using a slicer/filter seems a bit 'hacky'. Nice clear video though, as ever!
I have been saving this video for ages as I knew someday it will be useful and it just did!!! It saved me tons of headaches trying to put in place a requirement from the business. Thank you so much for sharing your knowledge with us!
The tip on sorting the table by hiding the field was worth its weight in Gold!!! It solved a problem for which I was seeking solutions from weeks. Thanks a ton for sharing :)
I'm just starting with Calculation Groups, and I agree that they are a powerful tool in the toolbox. Things I'm missing with them: sorting, ability to rename a specific column in a group for one visual.
This is awesome idea! This can introduce completely different usage scenarios without any complexity to the DAX and keeping performance to the maximum.
WoW just WoW!!!!! I do this for a living daily. This just compressed my data model a whole lot. Did I just have a "work" orgasm or what!!! I maintain 100s of measures PTD, YTD, MTD, FY for each of the measures. Sometimes my measure tables look so big i mistake them for my fact tables. I need just one calculation group and I am done. Reconstituting my data model this weekend just to surprise myself.
Very useful indeed. Quick question though. How do I go and invoke one of those measures from outside the calculationgroup. say I wan to use sales last year for a specific purpose but I want to write it as a measure instead. Can I go and leverage anything from the calculation group? Think about nested measures.
Excellent issue! I created a bunch of measures to obtain several measures from two anothers that do the "Top N + Others" calculation with two different variables. There are like 30 formulas for each group and it is hard for others to read them. Now, I can write (again) the 30 formulas just once and, with selected measures i can manage between the two variables. Gonna try. Thanks.
This is such a game changer. Currently we have to create these as separate measures, whereas we can just get users to know what group they need, rather than having to know which measures.
I wish there would be more videos explaining calculation groups, @Patrick. This is so good that I had to track back to this video after watching it a year ago!
I was hoping no one else had said it, but six days later, several beat me to it. Oh well, here's my spin... this is a metric ton of bananas, my friend! I can't wait to play with this!!
We have been using calculation groups heaps already! Fantastic functionality. We haven't been able to solve for a calculation group that presents the gross profit % (gross profit / revenue) across multiple measures - actual, budget and forecast. We haven't been able to combine this with a measure that presents the variance between actual and budget. Doing actual, budget and forecast gross margin % works fine down the column. But we can't get it to do actual vs budget or actual vs forecast.
You can create another Calculation Group called e.g. Scenario which holds the Actual, Budget and Forecast scenarios as Calculation Items. There you can also create a Calculation Item called e.g Variance as the difference between Actual and Budget. Be sure you understand the Precedence attribute when working with several Calculation Groups.
Hi, this was a great video, but how do we create a calculation group for those calculations where we have more than one measure in the formula. For Eg: A simple division: Result = A/B where we have one measure (A) in the numerator and another measure (B) in the Denominator?
how can we use this if I have a fiscal calendar and it is dynamic start and end dates. I have to go through the hierarchy. can you make a video on that?
Great video, it's really made it clear what is required to set up calculation groups, I'll enjoy getting to grips with them over the next couple of projects.
You just broke my mind, pretty amazing! Would've saved me so much time - I assume there's no way to vary the order in which they display and/or change the names in charts like we do with other measures? Maybe w/ additional columns...?
Nice one.. Thanks Patrick. Will conditional formatting work for individual calculation items?? Some thing like a variance field where we can highlight based on a condition... Let us know what you think abt it..
I would say yes without testing 😊. I could use the name of the Calculation Item and base the condition on that. That is interesting. I will give it a test and either post response here or may be a video. Stay tuned!!!!!
Thank you @patrick @guyinthecube, I have implemented the calculation for a report but the complex nature of the requirement is to have 3 layers of drill down at row level and 3 layers of drill down at column level and all of them have time intelligence e.g., MTD, QTD, YOY against Actuals, Budget and Actuals v Budget information per selected criteria e.g., Net sales, Gross Sales, PBT et cetera. The performance just drags completely and just fails later after hitting threshold. Could there be anything that can be done to improve the performance? The measures uses keep filters, selected measure with time intelligence calculation. Needless to say it is a matrix view. Any tip, suggestion would help.
This looks like a very cool feature for ... Eases the job of the Dataset developer for sure. My mind however, goes to the End user who did not create the model but has to build reports from the model. Thinking that this saves time on the development of the model but maybe adds "complexity" for Report builders... Creates a need for good documentation on the model and "Calculation" groups... The report builder would not see the explicit measures for each item and then the variations, but rather one item and then need to filter for the specific measure calculation they need... In our "world" we have one or two people that build Shared Datasets and then many many people that build hundreds of reports off those models. I see a challenge in "training" new report developers. Suggestions on how to bake in some kind of clarity to a new report developers as to the "guts" of what is in the calculation group?
Thanks a lot for the video. Is there a way of using Tabular editor for calculation difference, sum or other aggregation of 2 or more different measures?
so do these calculation groups get effected by slicer selection? lets say i have a date slicer and i have selected a date range..so do all these calculation groups get effected by the date slicer selection?
I had simulated calculation groups in ssas tabular 2016, and have been using it since then, as it didn't had sets concept. July release made it much easier. 👍 Guy in a cube on fore arm 👊
Is it possible to reference previously defined Calculation Item in next Calculation Items? It looks when we calculate variance we need to keep adding the same definitions many times.
Is there a way to aggregate a single column when there are multiple rows for the row selection? When I do a straight divide it gives me the correct number times the number of days in the month. I’m using scenario, Month start date, week start date, and day date in columns, and Client and country in rows.
Great Explanation🙌, It saved a lot of time. But now I also want to do conditional formatting on different columns. For example my YTD column should be of yellow color and MTD column should be of Green. how to perform this?
That's nice! What about if I put on matrix several measures: sales, customers and profit. And I need to show current value, previous month and difference?
You can connect to any model either SSAS or local power bi file, just make sure the file is open on the same machine tabular editor is running on. I use the portable version, so just unzip and run manually, doesn't require admin to install on the machine. github.com/otykier/TabularEditor/releases/download/2.11.7/TabularEditor.Portable.zip
I'm quite new to PowerBi and have been fighting with my employer to get properly trained in this software, but what I've already learnt from following you guys is better than any training I had so far!
This is so cool! Thanks Patrick. I have a "Can't display the visual" message. It says can't determine relationships between the fields. What am I doing wrong?
I have used calculation groups using the method from Enterprise DNA, where you basically create a table, then add a measure to it, remove the single column and close and open the side panel so that it changes the icon to a 'Calculation Group' icon. I put my measures in there. I didn't realize you can create subfolders in that though. But what if you just want to create a calculated field for a single measure instead of 'selectedvalue'? Does that show up and display correctly?
Thank you very much for these videos, they have been a great help. I liked this video as it helped me remove over 60 measures however I am wondering how or if the calculation items can be used in other visuals. I have for example a line and stacked column chart where the line is (active employees YoY % Var) of a measure other than what is covered in the columns (New Hires and New Hires SPLY by month). Do I have to recreate my individual measures to use them in this fashion?
@patrick: I am using SAP HANA as a data source and I have created calculation groups but after the refresh, all my measures changed to dimensions, so this will not apply to SAP HANA sources?
@@GuyInACube I am stuck at a scenario Patrick, The issue is to show images in visual where the pictures are in your local system. Can it be done in Power BI bcoz through web link one can do it easily. ??
Now this is bloody useful...If I didn't see it before, I do now. The external tools integration is really going to open up this puppy now. Exciting times! Only problem now is time to actually integrate all these cool developing features.
Thanks for this, game changer for sure - curious, is there a way to reference one of those calculation group measures in a card? i.e. if you just wanted to show a card with MTD
This is so useful is SO MANY WAYS! Having these calculation groups pre-created for my future reports. Thanks Patrick!
I teach a simple introduction class for an accounting product and I always recommend you guys as a resource for those looking for videos that are easy to follow. I learn new things from your channel as well. I love the short videos as it sparks ideas for things I can use to help me build dashboards for my own projects and gives me great direction for when someone asks about YoY, now I don't have to tell them to do as many DAX. So helpful!! AS ALWAYS.
I was able to reduce the number of measures from 178 to 61 thanks to calculation groups! This is so useful!
Patrick. Thanks. Good stuff! Good to note that implicit measures are no longer available if you add a calculation group. I know that explicit measures are best practice, but that can be a surprise to those unaware of that behavior.
Limitation or drawback of using calculation groups is that you may be unable to apply conditional formatting by measures. For example, in Patrick's example @ 6:44 , what if you wanted to conditionally format a gradients YoY column that applies a gradient theme on most negative to positive amounts? Let me know if someone has a workaround it.
I have submitted this as an idea ideas.powerbi.com/ideas/idea/?ideaid=2bbc3ffd-78f2-ea11-bf21-0003ff52cdee.
Is there a workaround it ??
This is great! I used to manually build slicer tables and construct switch measures to replicate that kind of behaviour, but this is much more scalable.
WOW, JUST WOW.... EXACTLY WHAT IVE BEEN LOOKING FOR, MADE MY DAY! THANKS PATRICK!!
Love it! Glad it made your day Ricardo! 👊
I like it, but the lack of actual measures appearing in the calculation group will confuse users to no end. Would be great if they would just auto-generate named measures that could be dragged into visuals individually, using a slicer/filter seems a bit 'hacky'.
Nice clear video though, as ever!
Good learning. You both are doing a great service by en lighting the people like us.
Hey Nags, your video is pretty helpful too!
I have been saving this video for ages as I knew someday it will be useful and it just did!!! It saved me tons of headaches trying to put in place a requirement from the business. Thank you so much for sharing your knowledge with us!
The tip on sorting the table by hiding the field was worth its weight in Gold!!! It solved a problem for which I was seeking solutions from weeks. Thanks a ton for sharing :)
Finally I see these calculation groups that caused such a fuss in the community in action :)
Woot! Thanks for watching 👊
I'm just starting with Calculation Groups, and I agree that they are a powerful tool in the toolbox. Things I'm missing with them: sorting, ability to rename a specific column in a group for one visual.
This is awesome idea! This can introduce completely different usage scenarios without any complexity to the DAX and keeping performance to the maximum.
Indeed! It's a game changer 👊
This is definitely a game changer for me. Brilliant! Thanks for sharing.
Awesome to hear! 👊
as just about everyone else has said..... "WOW!". Thanks Patrick
Appreciate that Spencer! 👊
This is really amazing, Thank you so much Patrick for introducing it.
What do I do when I have a question on anything Power BI?????? I head out here!!! You effing beauty - that cleared up so many things! Cheers
wow! Love to hear that! Glad it was helpful for you. 👊
Hi Patrick, Thanks for always sharing the tips. I feel like I discover gold here when I watch videos from your channel.
Thanks for putting this together! Very helpful!
Wow, one of the best improvements to my meager power bi skills, I love this , thanks!
WoW just WoW!!!!! I do this for a living daily. This just compressed my data model a whole lot. Did I just have a "work" orgasm or what!!! I maintain 100s of measures PTD, YTD, MTD, FY for each of the measures. Sometimes my measure tables look so big i mistake them for my fact tables. I need just one calculation group and I am done. Reconstituting my data model this weekend just to surprise myself.
This is pure gold! Thank you!!
Very useful indeed. Quick question though. How do I go and invoke one of those measures from outside the calculationgroup. say I wan to use sales last year for a specific purpose but I want to write it as a measure instead.
Can I go and leverage anything from the calculation group?
Think about nested measures.
Yes, you can filter expressions using a calc group item. The CG is just a table.
Great video, thanks Patrick.
Is there any way to add conditional formatting to individual columns when using this approach? Thanks.
So awesome to be able to build this in PowerBI- just like you can in SSAS!
Yes! This has been a long awaited feature.
Excellent issue! I created a bunch of measures to obtain several measures from two anothers that do the "Top N + Others" calculation with two different variables. There are like 30 formulas for each group and it is hard for others to read them. Now, I can write (again) the 30 formulas just once and, with selected measures i can manage between the two variables.
Gonna try.
Thanks.
BIG thumbs up! See alot potential in that concept! Thanks for explaining it in an easy understandable way!
Most welcome! Thanks for watching 👊
This is so easy to follow. Now I can create better Power BI reports. Awesome! Thanks Patrick.
This is such a game changer. Currently we have to create these as separate measures, whereas we can just get users to know what group they need, rather than having to know which measures.
Mind blowing. Thank you Patrick for explaining it beautifully!
I wish there would be more videos explaining calculation groups, @Patrick. This is so good that I had to track back to this video after watching it a year ago!
Thanks a lot Patrick! Perfectly explained!!
I was hoping no one else had said it, but six days later, several beat me to it. Oh well, here's my spin... this is a metric ton of bananas, my friend! I can't wait to play with this!!
HAHA all good! We agree with you 👊
This is so awesome. Thank you for a great tip.
We have been using calculation groups heaps already! Fantastic functionality. We haven't been able to solve for a calculation group that presents the gross profit % (gross profit / revenue) across multiple measures - actual, budget and forecast. We haven't been able to combine this with a measure that presents the variance between actual and budget. Doing actual, budget and forecast gross margin % works fine down the column. But we can't get it to do actual vs budget or actual vs forecast.
You can create another Calculation Group called e.g. Scenario which holds the Actual, Budget and Forecast scenarios as Calculation Items. There you can also create a Calculation Item called e.g Variance as the difference between Actual and Budget. Be sure you understand the Precedence attribute when working with several Calculation Groups.
@@simenjohannesen4647 is there any examples on how to solve this?
This is really awesome! Thank you so much!
Thank u Patrick, How we can group Rows (not measure) in matrix with calculation group? Many row in matrix is problem too.
Hello, Patrick. That was nice, thank you. But what about different colour formating for each column in this case?
Is there any other way to do so, in old version its kind helpfull if it works
Hi, this was a great video, but how do we create a calculation group for those calculations where we have more than one measure in the formula. For Eg: A simple division: Result = A/B where we have one measure (A) in the numerator and another measure (B) in the Denominator?
Download the sample file and look at the Sales YOY% measure
How do we apply conditional formatting on the values in this matrix? Is this a limitation of this approach or there is a way?
how can we use this if I have a fiscal calendar and it is dynamic start and end dates. I have to go through the hierarchy. can you make a video on that?
Great video, it's really made it clear what is required to set up calculation groups, I'll enjoy getting to grips with them over the next couple of projects.
Dude you just got yourself a huge fan! Thx for that!
WOW! Appreciate that a LOT! 👊
Amazing!!!! Will solve many of the present ways of creating measures
Can you please tell about the keyboard shortcuts to access dates, month and year only with keybaord.
This is great. Thanks for creating this video. Helps a lot!
Wow!! Great stuff!! So useful. Thanks a lot!
Wow, this is awesome!!! thanks Patrick!!!
You just broke my mind, pretty amazing! Would've saved me so much time - I assume there's no way to vary the order in which they display and/or change the names in charts like we do with other measures? Maybe w/ additional columns...?
There is an "Ordinal" field that will allow you to change the order that they are shown.
im getting the message cannot determine relationships between fields when I do this. Can anyone help on this?
Nice one.. Thanks Patrick. Will conditional formatting work for individual calculation items?? Some thing like a variance field where we can highlight based on a condition... Let us know what you think abt it..
I would say yes without testing 😊. I could use the name of the Calculation Item and base the condition on that. That is interesting. I will give it a test and either post response here or may be a video. Stay tuned!!!!!
Thank you Patrick!!
Curious if anyone figured this out.....Seems like it could be done in the expression formating.
This is awesome! Thanks Patrick
Awesome feature!! Thank you for that!
Well done Patrick.
This is amazing! Love your videos. Thanks for sharing!
Appreciate that! Thanks for watching👊
I love the way you explain things, like we were dudes, pals, friends, that's really nice, haha cheers from Brazil
can you let me know as to what is the syntax for calculate prior year same month value in calculation group in tabular editor
Simply Amazing, this makes development so much faster.
It can definitely help 👊
Amazing. Thank you for this demo
Most welcome Najim! 👊
Can you do this if the dataset your creating is using multiple dates order date, sales date, etc?
Thank you @patrick @guyinthecube, I have implemented the calculation for a report but the complex nature of the requirement is to have 3 layers of drill down at row level and 3 layers of drill down at column level and all of them have time intelligence e.g., MTD, QTD, YOY against Actuals, Budget and Actuals v Budget information per selected criteria e.g., Net sales, Gross Sales, PBT et cetera.
The performance just drags completely and just fails later after hitting threshold. Could there be anything that can be done to improve the performance? The measures uses keep filters, selected measure with time intelligence calculation. Needless to say it is a matrix view. Any tip, suggestion would help.
This looks like a very cool feature for ... Eases the job of the Dataset developer for sure. My mind however, goes to the End user who did not create the model but has to build reports from the model. Thinking that this saves time on the development of the model but maybe adds "complexity" for Report builders... Creates a need for good documentation on the model and "Calculation" groups... The report builder would not see the explicit measures for each item and then the variations, but rather one item and then need to filter for the specific measure calculation they need...
In our "world" we have one or two people that build Shared Datasets and then many many people that build hundreds of reports off those models. I see a challenge in "training" new report developers. Suggestions on how to bake in some kind of clarity to a new report developers as to the "guts" of what is in the calculation group?
Thanks a lot for the video. Is there a way of using Tabular editor for calculation difference, sum or other aggregation of 2 or more different measures?
so do these calculation groups get effected by slicer selection? lets say i have a date slicer and i have selected a date range..so do all these calculation groups get effected by the date slicer selection?
I had simulated calculation groups in ssas tabular 2016, and have been using it since then, as it didn't had sets concept. July release made it much easier. 👍
Guy in a cube on fore arm 👊
Thank Patrick, just wow
Is it possible to reference previously defined Calculation Item in next Calculation Items? It looks when we calculate variance we need to keep adding the same definitions many times.
Is there a way to aggregate a single column when there are multiple rows for the row selection? When I do a straight divide it gives me the correct number times the number of days in the month. I’m using scenario, Month start date, week start date, and day date in columns, and Client and country in rows.
Why does the external tools tab not appear in the september version?
This is very informative. Can the calculation group be created by any other external tools like - Dax Studio?
Awesome Patrick!!
Great Explanation🙌, It saved a lot of time. But now I also want to do conditional formatting on different columns. For example my YTD column should be of yellow color and MTD column should be of Green. how to perform this?
This is very useful in my organisation as i have a hell lot of measures needs to be created for all the time intelligence calculations.
That's nice! What about if I put on matrix several measures: sales, customers and profit. And I need to show current value, previous month and difference?
That works too.
But not nested. Let’s say: Sales, Sales PY, Customers, Customers PM, Customers change %. So different time intelligence for customers and for sales...
Amazing video, thanks.
Hi, thank you for ur good tutorials.
Can we do dimensions group instead of calculation group in Tabular Editor?
can they be used in scorecards (or some other visual component), and how?
Do you have any videos on opening models in Tabular Editor? We are in PBIRS and only on the May2020 version right now. So, no external tools.
You can connect to any model either SSAS or local power bi file, just make sure the file is open on the same machine tabular editor is running on. I use the portable version, so just unzip and run manually, doesn't require admin to install on the machine. github.com/otykier/TabularEditor/releases/download/2.11.7/TabularEditor.Portable.zip
I freaking love this!!! You've just shown me a great solution to what I was trying to implement in my reports!!
I'm quite new to PowerBi and have been fighting with my employer to get properly trained in this software, but what I've already learnt from following you guys is better than any training I had so far!
This is so cool! Thanks Patrick. I have a "Can't display the visual" message. It says can't determine relationships between the fields. What am I doing wrong?
This is an awesome idea. Thanks, Patrick
Awesome video!
WOW! This is so powerful, love it!
Awesome, thank you so much for this
I have used calculation groups using the method from Enterprise DNA, where you basically create a table, then add a measure to it, remove the single column and close and open the side panel so that it changes the icon to a 'Calculation Group' icon. I put my measures in there. I didn't realize you can create subfolders in that though. But what if you just want to create a calculated field for a single measure instead of 'selectedvalue'? Does that show up and display correctly?
6:28 Anyone has an idea why totals for MTD do not appear in matrix while they do for YTD? Thanks, Patrick!
how to set up the conditional formatting rule for various columns?
you just saved a lot of time for me.. I was supposed have 200+ metrics.. Also we can have these as builtin templates in powerbi in future probably...
Nice trick.. what can i do if i have to see MTD or YTD for both Sales and Revenue side by side using this approach?
That's Great, thanks for the excellent content 🙌
Thank you very much for these videos, they have been a great help. I liked this video as it helped me remove over 60 measures however I am wondering how or if the calculation items can be used in other visuals. I have for example a line and stacked column chart where the line is (active employees YoY % Var) of a measure other than what is covered in the columns (New Hires and New Hires SPLY by month). Do I have to recreate my individual measures to use them in this fashion?
@patrick: I am using SAP HANA as a data source and I have created calculation groups but after the refresh, all my measures changed to dimensions, so this will not apply to SAP HANA sources?
Can we use Calculation Groups in Excel Data Models that use Excel Data Sources?
What did u mean by doing it dynamically using calculation group as u said at last??
Video coming for that. Stay tuned 👊
@@GuyInACube I am stuck at a scenario Patrick,
The issue is to show images in visual where the pictures are in your local system.
Can it be done in Power BI bcoz through web link one can do it easily.
??
Great explaination !
Am I using Calculation Groups? I AM NOW!
YES! They can be a great option!
Now this is bloody useful...If I didn't see it before, I do now. The external tools integration is really going to open up this puppy now. Exciting times! Only problem now is time to actually integrate all these cool developing features.
could you explain how to conditionaly hide or show a column in a matrix regardless of empty columns?
Thanks for this, game changer for sure - curious, is there a way to reference one of those calculation group measures in a card? i.e. if you just wanted to show a card with MTD
Perhaps using filters at the visual level ?
Yes, put a filter on the visual. You can also put such a filter on a Calculate expression.