I have seen plenty of tutorials in different areas, but this is way far for what I have seen. Extensive and simple explanation to all of the necessary things about calculated groups. I remember that I was calling this DateTool into Multidimensional model using scopes.
There are no enough words in my vocabulary to describe how GREAT you are. Thank you very much for your great job. Hope one day I can visit your lectures and ask a looot of questions:-)thanks for your video and your books.
Thank you so much Marco. I have probably watched this video over 10 times. Other than just experimenting with these new features I finally figured out how to not show blank data as well as learning the power of the role of SELECTEDVALUE DAX as a "place holder" for any measure. This was awesome Marco. Thank you sir!
This is so good! So easy to understand. Easily the best tutorial on the subject I've seen. Thank you so much for this and everything you do to advance Power BI. You guys are the best (granted, I'm not saying anything we don't all know)!
Thank you, Marco. great explanation and very useful. Can the calculation groups be used in row context where different metrics are presented in matrix table.
Wow! Thank you so much for these illustrations. I waste writing measures. This will save me so much time. I also took note of the “selectedmeasure” Dax measure applicability. Thank you Thank you Marco.
Great!! Yes it's really powerful and need to use it carefully on big models.. However, power bi desktop July 2020 got external tools, still ideally not embedded inside power bi. Wish it could have been all in one application at least for creation.. Thought about it when measure tools came in the new office ribbon and was hoping somethings will be added in that.. But still a great feature.. thank you 😊 chao
Marco it's perfect!👍, i wish great luck, and growing for this channel , specially for those who they want to learn but not have money to buy paid video courses Thanks lot for your great explanation
Thank you for the video, is super powerful. I had an issue related with percentage, the measure I dragging in is computed as a entire number, so my YOY% doesnt not come as a percentage. How can I solve it ? Thanks
Thanks Marco! The Defined guide book (Chapter 9) should have a link to this video and/or viceversa your video here / articles on SQLBI should has a reference to the book ( Chapter or Page)
We already have all the links you asked on the website! The video has links in the description, the articles on SQLBI have a link to the videos. We just cannot change the printed book! :)
Yes you can use copy and paste: 1 - select the calculation item to copy 2 - Edit / Copy from the main menu 3 - select the Calculation Group table (not the calculation items node) 4 - Edit / Paste from the main menu
Thanx for the video, Marco! A question: is it possible to dynamically change the name of the calculation item? E.g. If we are displaying columns for the ”current week”, ”current week +1”, ”current week+2”, etc, can we instead of these static names display the actual week numbers? Maybe a potential follow-up video topic? :) Many thanx for all you do! ❤
Can we select measures via filters so that you don't have to drag measures to the table? For example, instead of manually dropping the sales or cost measures to the table, can we list available measures to add that table?
Thank you SQLBI and Marco. I have two questions. 1. If we are consuming the model from Excel (applied to the AAS model connecting from Excel to generate a pivot table), what is the implication? 2. What would be other use cases like we would like to have more than one calculation group in a single model? Thanks again.
1. It just works, and Excel provides the best consumption experience for calculation groups (also read: www.sqlbi.com/blog/marco/2020/08/01/arbitrary-combination-of-measures-and-calculation-items/) 2. See examples at www.sqlbi.com/articles/understanding-calculation-group-precedence/
Thanks Marco for this great video! I have a question, Is it possible to select only some of the calculation items present in the calculation group? For ex, I need PY and YOY only to be displayed from the calculation group. Can we select what items have to be displayed from the calculation group?
I had a calculation group for time intelligence and want to copy it to another report via create script but it give syntax error for the script. What is the solution? create script is like; { "createOrReplace": { "object": { "database": "027c4.....
Just another short question, after adding a calculation group to my report, I'm missing the summarize (sum,avg,min,...) functionality in the fields tab (right click on an field). Is this because Tabular Editor changes the model here so that PBI can no longer offer this function?
It is because calculation groups can be used only if you disable implicit measures. I suggest you read the full articles: www.sqlbi.com/calculation-groups/
Hi Marco, I'd like to hear your thoughts on this - assuming that I need to iterate a simple calculation such as SUM( TableA[TotalSales] ) across multiple tables, is there a way to do this with Tabular Editor? Here, it is safe to assume: 1. Column names across all tables are the same, i.e.: [TotalSales] 2. Due to the different nature of the data structure, it is impossible to merge all tables into one long table I've checked out many articles and they've only been explaining how the measures can be manipulated for elements within the same column, in the same table. Now, as at the time of me repeating my questions a few times to discuss with my peers, I am more convinced to create a table (either in SQL/DAX/M) to append all the relevant fields that I need, and run a DAX measure. I know that it is possible to do the table appending in a SQL example by using UNION ALL, and I am almost certain DAX has the same capabilities too - just that I've never tried it before. Your thoughts please?
You cannot use meta-programming in DAX, but you can create scripts in C# running in Tabular Editor that create ad-hoc DAX measures based on existing metadata. See docs.tabulareditor.com/Advanced-Scripting.html
Thank you very much for these clear explanations. I know now why and how to use calculation groups but I have a question: how to use only one measure in a graphic for example instead of all measures in the calculation group? I see the calculation groups like a way to write more measures in a few click (maybe I'm wrong) but if I have to write every single measure I think I'll lose all the benefits of this technic... Thank you again,
Calculation groups are a way to apply the same transformation to many different measures without having to write too many measures. I suggest you read the articles linked in the comments to find more examples.
Amazing Video, crystal clear! A quick question, Marco- Do you know roughly when Calculation groups support will come to Excel? It would be awesome to be able to use 'Analyze in Excel' and the likes with a model that uses Calculation Groups.
Thanks for this useful video, I have a question when following steps. There is blank for my YOY% and YOY=Current value even I used same formulas as yours. Do u have any clues why would this happened?
I have 4 pricing type 12 sales total - Contract , Standard, buyin, Override. We want to see sales impact by price increase 1%, 1.5%, 2% and so on. but want to exclude buyin and override from calculating but they should return 12 sales total without any increase. So my total should be Contract , standard with price increase and Buying, override without price increase. I used calculate columns to use "If" and "switch"function but the result is not dynamic when i change % increase.
Do I understand it correctly that when using calculation groups you can not display columns in any order? I.e. I can not display Sales, Margin, Sales PY, Margin PY, ... It has to be all Sales measures, then all Margin measures and so on ?
Thanks Marco.how can I create PY calculation / same period last year function for custom 445 weekly calendar in which has different start and end dates.
How to go about if I only want to use let's say just YOY and YOY% for now, but I will also need PY. Or I need to make use of all the Time Intelligence measures that were created in the calculation group at once? I hope there's a way to select which specific TI measure is required for a specific visualization. Maybe there is, I just don't know how? Thanks!!
With Excel you can create a named set, whereas Power BI does not have a good user interface for this. Read www.sqlbi.com/blog/marco/2020/08/01/arbitrary-combination-of-measures-and-calculation-items/ and vote the related idea!
Hi Marco, This is not related to this topic but I have a Q. Are Collapsible Columns technically possible? Also, I don't know why but since I started using the group calculations, I have this folder that says " Format String Measure" which doubles all my DAXES and can't seem to get rid of it. Any idea?
You should vote this idea, it's a feature missing in Power BI: ideas.powerbi.com/ideas/idea/?ideaid=151a8279-d892-4e52-8cec-4f3b69bdbc6c The folder you have contains the internal measures returning the format string if you customize the Format String in calculation items - you could remove the custom format strings, but that folder and the measures inside are hidden, it shouldn't be a big issue for the end users.
I am using this calculation group. this is very helpful. I am facing a problem when i export it from power bi service. the problem is - I see the measures like CY,PY,YoY,YoY% CY reach% to PY in columns in the visuals, but when i export this to an excel then these measures are not exported as columns but they are turned into rows and also changes the number formats from %s to numbers. Can you please let me know if you have a solution for this?
Unfortunately no - that is managed by Power BI, which does not have an ideal experience in consuming calculation groups (which is a feature of the engine, which is also used in other products like Analysis Services and consumed by client like Excel). One workaround (not really a solution) is using queries executed in Excel, even though this specific approach doesn't solve the custom format issue: ruclips.net/video/L-IfcHk4w3I/видео.html
Technically, a calculation item is just a value in a column of a table (the calculation group). I would say no - even though you could sort by the value of a measure that is modified by the calculation item selected, and in that case the answer would be yes...
Hi, can you use Calculation groups in a line chart? For instance I'd like to show current YTD as one line and last year YTD as second line. When I drag in my calculated group it doesn't appear to create > 1 line.
Dear sir , I have updated version of Power Bi as well as Tabular editor , but still in external tool option Tabular editor button remains inactive (light in colour ) please suggest so I can start using the same
You have to enable the preview feature “Store datasets using enhanced metadata format” in the Power BI options in order to create calculation groups. Check here: www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/
I tried but I have an issue - Only calculated table options is available in tabular tools when I import the model - Creating a calculated group disables all measures in the model. No more sigma next to the measures - The icon in desktop for the calculated group looks just like the other tables
Hi Sir, how can we do this on columns instead of measures? I have data in which there are multiple columns on which similar operations need to be performed. But they are fact tables from source data not calculated measures.
@@SQLBI I followed your video and instructions form your great book, now I would like to do a calculation (in tabular editor) which calculates the market share for each month. Since I cannot attach an image example; Jan had 10% of the total sales Feb: 15, Mar 18% etc, and total would be 100%.
can you let me know as to what is the syntax for calculate prior year same month value in calculation group in tabular editor. example, i want to know value of may 2021
Hi, Thanks for sharing the some materials to refer. Unfortunately, i was unable to find the solution for my problem. I am using the below DAX in Tabular editor for below calculations MTD : CALCULATE ( SELECTEDMEASURE (), DATESMTD ( 'Power BI Date'[Date] ) ) Current year YTD: CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Power BI Date'[Date] ) ) Last Year YTD: CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Power BI Date'[Date] ) ) but, not getting the DAX for calculating same moth last year's value. In my case, the current year MTD is May 2021. I want to get May 2020 MTD (only may).. If you have got some idea, can you share.
The article has the answer, you just have to modify Sales PMTD using YEAR instead of MONTH in line 7. However, you should rewrite the other calculations too and adapt the date table including the DateWithSales calculated column.
Hi all - I'm still learning (lots) but I'm having trouble getting my calculation group for current and previous year to format in thousands to this £123k from this (£123,123.00). I'm sure this is simple to do, but after reading looking online, and see possible solution I'm still unable to resolve. Could I please ask for help?
Write in related comments of articles on www.sqlbi.com using the sample file attached, so it's easy to reply with answers that can be helpful to other readers. Thanks!
Hey Marco thanks for enlightening me with this feature I have a Doubt Measures: Revenue, COGS, Gross Margin, GM% Calculation Group: Forecast, Budget, Variance Want to custom format Revenue and COGS as 0.00M Tried "#,##0,,.00M" Calculation Groups not working when I am custom formatting my measures When I am using pre defined format like "Fixed" then Calculation Groups are working. Pls Help!!
I think I have found out why calculation groups are not working. The thing is when I custom format Revenue/COGS into "#,##0,,.00M" it converted that into "Text" Datatype. So I can't do further calculations on Text datatype column. I still don't know the solution for this. Please help.
I don't think the format you wrote is supported, and probably that is causing the issue. Try a simpler format, if it works that way then you have to find a supported format string.
@@marcorusso7472 i'm sorry for my brief question/comment. the aim is to create calc group for a moving average measure such as the following for weekly average: CALCULATE ( AVERAGE( SalesINet[SalesAmount] ), DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), -7, DAY) ) i get an error stating 'the AVERAGE function only accepts a column reference as an argument' when i edited above dax formula to: CALCULATE ( AVERAGE( SELECTEDMEASURE() ), DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), -7, DAY) )
@@nikahafiz1 By using AVERAGE ( column ) you perform an average of the transactions, whereas in a moving average you are likely to create moving average of the daily amount, which should be obtained grouping by date first. I would expect something like AVERAGEX ( DATESINPERIOD ( ... ), [measure] ) where the measure is a calculation that aggregates the value you need (e.g. SUM ( Sales[Amount] ) ) If you really want to do the average by transaction, you need to use the column and you cannot use this approach, but it would be the first time I see such a requirement.
Just install the latest versions of Tabular Editor and DAX Studio, you will see the tool activated on Power BI Desktop after that (you need Power BI Desktop July 2020 or later version).
Is it possible to show different measures side-by-side in a matrix if I want to use calculation groups? For example, I want to show on columns: Revenue, Revenue YoY, Revenue YoY %, Profit, Profit YoY, Profit YoY% I have created a Calculation Group with YoY and YoY %, but I need to show them for both - Profit and Revenue measures. Is it possible? Or do I still need to create all these one-by-one?
I have a dream ... that one day there will be a decent external tool to replace the DAX editor in Power BI. Something like DAX Studio but which lets you type in DAX expressions instead of DAX queries. Or just wait for some time until Daniel gets to add intellisense to Tabular Editor.
@@marcorusso7472 Yey, cool.. can't wait. If you stop to think about it, this external tool feature is much bigger than it may sound at first glance. Initially Power BI was much thinner on the visuals part than competitors, so they created the marketplace to catch up. And hundreds of freelance developers and smaller developer boutiques quickly improved the visuals offerings. Now, with external tools, in a year or two the same might happen for the Power BI developers thanks to this integration.
@@marcorusso7472 It will be nice if it was a button for backuping file with timestamp in the name for each backup file and with the ability to select folder to save. If someone would make such a button in external tools bar... =)
I have seen plenty of tutorials in different areas, but this is way far for what I have seen. Extensive and simple explanation to all of the necessary things about calculated groups. I remember that I was calling this DateTool into Multidimensional model using scopes.
Tabular Editor is such a savior. Thanks, Marco for the explanation.
There are no enough words in my vocabulary to describe how GREAT you are. Thank you very much for your great job. Hope one day I can visit your lectures and ask a looot of questions:-)thanks for your video and your books.
Thank you so much Marco. I have probably watched this video over 10 times. Other than just experimenting with these new features I finally figured out how to not show blank data as well as learning the power of the role of SELECTEDVALUE DAX as a "place holder" for any measure. This was awesome Marco. Thank you sir!
Glad it was helpful!
I am also coming back to calculation groups! It's flexible, useful and almost feels like magical. it's like another PBI inside .pbx
Actually is "Selectedmeasure()"
This is exactly what tabular models have been missing that multidimensional models had. I can't wait to add this to my models! Excellent! Thank you!
So much in love with your lessons.
Thank you, Marco! It is great and interesting solution. This is what was VERY lacking in Power BI and Power Pivot.
Thank you, Marco! Useful and very interesting solution. You are one of the best In Power BI & DAX.
Really great improvement. Not sure how we can implement this yet, but it makes me smile.
Thorough explanation in simplest way! Thank you, Marco!
Thank you Marco for the video update. These video/articles are very clear and your example was a great first start for me.
This is great. Thanks, Marco!
This is so good! So easy to understand. Easily the best tutorial on the subject I've seen. Thank you so much for this and everything you do to advance Power BI. You guys are the best (granted, I'm not saying anything we don't all know)!
Thank you, Marco. great explanation and very useful. Can the calculation groups be used in row context where different metrics are presented in matrix table.
Thanks a lot! Great explanation, if I want to show a part of the group instead of the whole one, is there a way I could?
Wow! Thank you so much for these illustrations. I waste writing measures. This will save me so much time. I also took note of the “selectedmeasure” Dax measure applicability. Thank you Thank you Marco.
i was literally thinking about how to do this - so cool!
Thank you, Marco! Great and very clear explanation.
Hi Marco is this just available in power bi or is it available in tabular models aswell? Thanks
It is available in Analysis Services 2019 and Azure Analysis Services, too.
Great!! Yes it's really powerful and need to use it carefully on big models.. However, power bi desktop July 2020 got external tools, still ideally not embedded inside power bi. Wish it could have been all in one application at least for creation.. Thought about it when measure tools came in the new office ribbon and was hoping somethings will be added in that..
But still a great feature.. thank you 😊 chao
Marco it's perfect!👍, i wish great luck, and growing for this channel , specially for those who they want to learn but not have money to buy paid video courses
Thanks lot for your great explanation
Very useful tutorial. Thank you :-)
Thank you for the video, is super powerful. I had an issue related with percentage, the measure I dragging in is computed as a entire number, so my YOY% doesnt not come as a percentage. How can I solve it ? Thanks
See www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/
Congratulations on the video and thanks for the explanations it really is an excellent tool.
Thanks Marco!
The Defined guide book (Chapter 9) should have a link to this video and/or viceversa your video here / articles on SQLBI should has a reference to the book ( Chapter or Page)
We already have all the links you asked on the website! The video has links in the description, the articles on SQLBI have a link to the videos.
We just cannot change the printed book! :)
Marco, thank you a lot!
Could the calculation group be copied to another PBI file?
Yes you can use copy and paste:
1 - select the calculation item to copy
2 - Edit / Copy from the main menu
3 - select the Calculation Group table (not the calculation items node)
4 - Edit / Paste from the main menu
@@SQLBI Thank you for your answer!
Thank you so much; If I could, I would put 100K like for this video. GRAZIE MILLE DAVVERO
Great video marco! Seems it be easier to use parameters for calc groups. Like a measure could be written as a function with parms. What do you think?
You don't need calculation groups for that! www.daxpatterns.com/parameter-table/
@@SQLBI I've actually used this pattern before. To change measures I used the switch function. Thanks for the great post and personal response!
Thanx for the video, Marco!
A question: is it possible to dynamically change the name of the calculation item? E.g. If we are displaying columns for the ”current week”, ”current week +1”, ”current week+2”, etc, can we instead of these static names display the actual week numbers?
Maybe a potential follow-up video topic? :)
Many thanx for all you do! ❤
No it is not possible to change them dynamically.
Can we select measures via filters so that you don't have to drag measures to the table? For example, instead of manually dropping the sales or cost measures to the table, can we list available measures to add that table?
Here, if anyone also wonders:
ruclips.net/video/xS1t25_GsWw/видео.html
Thank you SQLBI and Marco. I have two questions.
1. If we are consuming the model from Excel (applied to the AAS model connecting from Excel to generate a pivot table), what is the implication?
2. What would be other use cases like we would like to have more than one calculation group in a single model?
Thanks again.
1. It just works, and Excel provides the best consumption experience for calculation groups (also read: www.sqlbi.com/blog/marco/2020/08/01/arbitrary-combination-of-measures-and-calculation-items/)
2. See examples at www.sqlbi.com/articles/understanding-calculation-group-precedence/
Thanks Marco for this great video! I have a question, Is it possible to select only some of the calculation items present in the calculation group? For ex, I need PY and YOY only to be displayed from the calculation group. Can we select what items have to be displayed from the calculation group?
Yes, you can apply a filter to the visual or to the page. For Power BI, it is just another table with one column.
I had a calculation group for time intelligence and want to copy it to another report via create script but it give syntax error for the script. What is the solution?
create script is like;
{
"createOrReplace": {
"object": {
"database": "027c4.....
Thank you Marco. Really great tool. Just one question, when creating measures in Tabular Editor, I don't see IntelliSense activated? Is this possible?
Possibly a future version of Tabular Editor will get IntelliSense - you can find more details here: github.com/otykier/TabularEditor/issues/64
Just another short question, after adding a calculation group to my report, I'm missing the summarize (sum,avg,min,...) functionality in the fields tab (right click on an field). Is this because Tabular Editor changes the model here so that PBI can no longer offer this function?
It is because calculation groups can be used only if you disable implicit measures. I suggest you read the full articles: www.sqlbi.com/calculation-groups/
Hi Marco, I'd like to hear your thoughts on this - assuming that I need to iterate a simple calculation such as SUM( TableA[TotalSales] ) across multiple tables, is there a way to do this with Tabular Editor?
Here, it is safe to assume:
1. Column names across all tables are the same, i.e.: [TotalSales]
2. Due to the different nature of the data structure, it is impossible to merge all tables into one long table
I've checked out many articles and they've only been explaining how the measures can be manipulated for elements within the same column, in the same table. Now, as at the time of me repeating my questions a few times to discuss with my peers, I am more convinced to create a table (either in SQL/DAX/M) to append all the relevant fields that I need, and run a DAX measure.
I know that it is possible to do the table appending in a SQL example by using UNION ALL, and I am almost certain DAX has the same capabilities too - just that I've never tried it before.
Your thoughts please?
You cannot use meta-programming in DAX, but you can create scripts in C# running in Tabular Editor that create ad-hoc DAX measures based on existing metadata.
See docs.tabulareditor.com/Advanced-Scripting.html
The article from this video can be updated with the shiny DAX script for calculation groups
Thank you very much for these clear explanations. I know now why and how to use calculation groups but I have a question: how to use only one measure in a graphic for example instead of all measures in the calculation group? I see the calculation groups like a way to write more measures in a few click (maybe I'm wrong) but if I have to write every single measure I think I'll lose all the benefits of this technic... Thank you again,
Calculation groups are a way to apply the same transformation to many different measures without having to write too many measures. I suggest you read the articles linked in the comments to find more examples.
If we will apply % formatting then we will not able to apply conditional icon formatting is there any way ?
Great video!!
Amazing Video, crystal clear! A quick question, Marco- Do you know roughly when Calculation groups support will come to Excel? It would be awesome to be able to use 'Analyze in Excel' and the likes with a model that uses Calculation Groups.
You can already use Analyze in Excel, and it works very well with Calculation Groups!
Thanks for this useful video, I have a question when following steps. There is blank for my YOY% and YOY=Current value even I used same formulas as yours. Do u have any clues why would this happened?
I suggest you download the sample file from the article in the link and compare with your solution.
I have 4 pricing type 12 sales total - Contract , Standard, buyin, Override. We want to see sales impact by price increase 1%, 1.5%, 2% and so on. but want to exclude buyin and override from calculating but they should return 12 sales total without any increase.
So my total should be Contract , standard with price increase and Buying, override without price increase. I used calculate columns to use "If" and "switch"function but the result is not dynamic when i change % increase.
Amazing work !
Do I understand it correctly that when using calculation groups you can not display columns in any order? I.e. I can not display Sales, Margin, Sales PY, Margin PY, ... It has to be all Sales measures, then all Margin measures and so on ?
Yes, you have some limitation in arranging the position if you combine calculation items and measures in the columns of a matrix.
Thanks Marco.how can I create PY calculation / same period last year function for custom 445 weekly calendar in which has different start and end dates.
See this: www.daxpatterns.com/week-related-calculations/
How to go about if I only want to use let's say just YOY and YOY% for now, but I will also need PY. Or I need to make use of all the Time Intelligence measures that were created in the calculation group at once? I hope there's a way to select which specific TI measure is required for a specific visualization. Maybe there is, I just don't know how? Thanks!!
With Excel you can create a named set, whereas Power BI does not have a good user interface for this. Read www.sqlbi.com/blog/marco/2020/08/01/arbitrary-combination-of-measures-and-calculation-items/ and vote the related idea!
Hi Marco, This is not related to this topic but I have a Q. Are Collapsible Columns technically possible? Also, I don't know why but since I started using the group calculations, I have this folder that says " Format String Measure" which doubles all my DAXES and can't seem to get rid of it. Any idea?
You should vote this idea, it's a feature missing in Power BI: ideas.powerbi.com/ideas/idea/?ideaid=151a8279-d892-4e52-8cec-4f3b69bdbc6c
The folder you have contains the internal measures returning the format string if you customize the Format String in calculation items - you could remove the custom format strings, but that folder and the measures inside are hidden, it shouldn't be a big issue for the end users.
After two years is it now possible to refer to another item in calculation group without running into troubles?
It's the nature of calculation groups, DAX doesn't have full recursion so no differences.
I am using this calculation group. this is very helpful. I am facing a problem when i export it from power bi service. the problem is - I see the measures like CY,PY,YoY,YoY% CY reach% to PY in columns in the visuals, but when i export this to an excel then these measures are not exported as columns but they are turned into rows and also changes the number formats from %s to numbers. Can you please let me know if you have a solution for this?
Unfortunately no - that is managed by Power BI, which does not have an ideal experience in consuming calculation groups (which is a feature of the engine, which is also used in other products like Analysis Services and consumed by client like Excel).
One workaround (not really a solution) is using queries executed in Excel, even though this specific approach doesn't solve the custom format issue: ruclips.net/video/L-IfcHk4w3I/видео.html
" we never know..." LOL Great video! Thank's
Thank you! Is there a chance to sort a matrix by a calculation group item?
Technically, a calculation item is just a value in a column of a table (the calculation group).
I would say no - even though you could sort by the value of a measure that is modified by the calculation item selected, and in that case the answer would be yes...
How can I add conditional formatting to my measures in calculated group?
See www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/
Hi, can you use Calculation groups in a line chart? For instance I'd like to show current YTD as one line and last year YTD as second line. When I drag in my calculated group it doesn't appear to create > 1 line.
You should use the column of the calculation group in the category of the line chart. It should work.
Dear sir , I have updated version of Power Bi as well as Tabular editor , but still in external tool option Tabular editor button remains inactive (light in colour ) please suggest so I can start using the same
You have to enable the preview feature “Store datasets using enhanced metadata format” in the Power BI options in order to create calculation groups. Check here: www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/
I tried but I have an issue
- Only calculated table options is available in tabular tools when I import the model
- Creating a calculated group disables all measures in the model. No more sigma next to the measures
- The icon in desktop for the calculated group looks just like the other tables
Calculation groups disable implicit measures - see all the articles in the calculation groups series: www.sqlbi.com/calculation-groups/
Hi Sir, how can we do this on columns instead of measures? I have data in which there are multiple columns on which similar operations need to be performed. But they are fact tables from source data not calculated measures.
You cannot, calculation groups only work over measures.
Great video, Marco any idea how I could do market share measure on tabular editor?
What do you mean?
@@SQLBI I followed your video and instructions form your great book, now I would like to do a calculation (in tabular editor) which calculates the market share for each month. Since I cannot attach an image example; Jan had 10% of the total sales Feb: 15, Mar 18% etc, and total would be 100%.
See this: www.daxpatterns.com/hierarchies/
I'm connecting my tabular editor to my Tabular cube, and appears disable, the option create > Calculation Group Alt+7 :-(
It depends on your compatibility level, probably you should upgrade your model.
will this work if i publish it and only have PowerBi Pro - and not Premium ?
Yes it works with Power BI Pro, too!
can you let me know as to what is the syntax for calculate prior year same month value in calculation group in tabular editor. example, i want to know value of may 2021
See www.daxpatterns.com/standard-time-related-calculations/
Hi, Thanks for sharing the some materials to refer. Unfortunately, i was unable to find the solution for my problem.
I am using the below DAX in Tabular editor for below calculations
MTD : CALCULATE ( SELECTEDMEASURE (),
DATESMTD ( 'Power BI Date'[Date] ) )
Current year YTD: CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Power BI Date'[Date] ) )
Last Year YTD: CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Power BI Date'[Date] ) )
but, not getting the DAX for calculating same moth last year's value. In my case, the current year MTD is May 2021. I want to get May 2020 MTD (only may).. If you have got some idea, can you share.
The article has the answer, you just have to modify Sales PMTD using YEAR instead of MONTH in line 7. However, you should rewrite the other calculations too and adapt the date table including the DateWithSales calculated column.
Hi all - I'm still learning (lots) but I'm having trouble getting my calculation group for current and previous year to format in thousands to this £123k from this (£123,123.00). I'm sure this is simple to do, but after reading looking online, and see possible solution I'm still unable to resolve. Could I please ask for help?
Write in related comments of articles on www.sqlbi.com using the sample file attached, so it's easy to reply with answers that can be helpful to other readers. Thanks!
Hey Marco thanks for enlightening me with this feature
I have a Doubt
Measures: Revenue, COGS, Gross Margin, GM%
Calculation Group: Forecast, Budget, Variance
Want to custom format Revenue and COGS as 0.00M
Tried "#,##0,,.00M"
Calculation Groups not working when I am custom formatting my measures
When I am using pre defined format like "Fixed" then Calculation Groups are working.
Pls Help!!
I think I have found out why calculation groups are not working.
The thing is when I custom format Revenue/COGS into "#,##0,,.00M" it converted that into "Text" Datatype. So I can't do further calculations on Text datatype column.
I still don't know the solution for this.
Please help.
I don't think the format you wrote is supported, and probably that is causing the issue. Try a simpler format, if it works that way then you have to find a supported format string.
Just awesome.
what if i need to use calc group for a measure such as average which has to refer to column? it's sort of column-within-column....
Please, can you clarify the question? I don't understand what is the problem but the reference "column-within-column" is totally unclear to me.
@@marcorusso7472 i'm sorry for my brief question/comment. the aim is to create calc group for a moving average measure such as the following for weekly average:
CALCULATE (
AVERAGE( SalesINet[SalesAmount] ),
DATESINPERIOD( 'Calendar'[Date],
LASTDATE( 'Calendar'[Date] ),
-7,
DAY)
)
i get an error stating 'the AVERAGE function only accepts a column reference as an argument' when i edited above dax formula to:
CALCULATE (
AVERAGE( SELECTEDMEASURE() ),
DATESINPERIOD( 'Calendar'[Date],
LASTDATE( 'Calendar'[Date] ),
-7,
DAY)
)
@@nikahafiz1 By using AVERAGE ( column ) you perform an average of the transactions, whereas in a moving average you are likely to create moving average of the daily amount, which should be obtained grouping by date first. I would expect something like
AVERAGEX (
DATESINPERIOD ( ... ),
[measure]
)
where the measure is a calculation that aggregates the value you need (e.g. SUM ( Sales[Amount] ) )
If you really want to do the average by transaction, you need to use the column and you cannot use this approach, but it would be the first time I see such a requirement.
@@marcorusso7472 tks for the clarification/correction. still learning...
how do I activate the external tools tab?
Just install the latest versions of Tabular Editor and DAX Studio, you will see the tool activated on Power BI Desktop after that (you need Power BI Desktop July 2020 or later version).
@@SQLBI Thanks a lot! It is now working.
thanks
Is it possible to show different measures side-by-side in a matrix if I want to use calculation groups?
For example, I want to show on columns:
Revenue, Revenue YoY, Revenue YoY %, Profit, Profit YoY, Profit YoY%
I have created a Calculation Group with YoY and YoY %, but I need to show them for both - Profit and Revenue measures.
Is it possible? Or do I still need to create all these one-by-one?
would've be nice if you could paste those formulas instead of just briefly showing them
I don't see External Tools tab once I install Tabular Editor :(
Same
@@danjeon Make sure you install Power BI Desktop July 2020 first, and then install the latest version of Tabular Editor.
Make sure you install Power BI Desktop July 2020 first, and then install the latest version of Tabular Editor.
@@marcorusso7472 It worked as described. Great! Thank you, Dan
ruclips.net/video/jScicVBrr0s/видео.html
👏👏👏👏👏
I have a dream ... that one day there will be a decent external tool to replace the DAX editor in Power BI. Something like DAX Studio but which lets you type in DAX expressions instead of DAX queries. Or just wait for some time until Daniel gets to add intellisense to Tabular Editor.
I've seen the future and... just wait, not too much :)
@@marcorusso7472 Yey, cool.. can't wait. If you stop to think about it, this external tool feature is much bigger than it may sound at first glance. Initially Power BI was much thinner on the visuals part than competitors, so they created the marketplace to catch up. And hundreds of freelance developers and smaller developer boutiques quickly improved the visuals offerings. Now, with external tools, in a year or two the same might happen for the Power BI developers thanks to this integration.
@@1sefirot9 Definitely, it's the beginning of a new era!
@@marcorusso7472 It will be nice if it was a button for backuping file with timestamp in the name for each backup file and with the ability to select folder to save. If someone would make such a button in external tools bar... =)
What's the advantage of doing this versus a SWITCH function?
Performance and better code organization; ability to customize the format.
👋👋👋👋
Uau...