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.

Tabular Editor is such a savior. Thanks, Marco for the explanation.

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!

Thorough explanation in simplest way! Thank you, Marco!

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.

Thank you Marco for the video update. These video/articles are very clear and your example was a great first start for me.

Really great improvement. Not sure how we can implement this yet, but it makes me smile.

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.

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!

@SQLBI

3 жыл бұрын

Glad it was helpful!

@ajaaskelainen

2 жыл бұрын

I am also coming back to calculation groups! It's flexible, useful and almost feels like magical. it's like another PBI inside .pbx

@ricardocontreras

9 ай бұрын

Actually is "Selectedmeasure()"

Thank you, Marco! Great and very clear explanation.

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)!

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

So much in love with your lessons.

i was literally thinking about how to do this - so cool!

This is great. Thanks, Marco!

Congratulations on the video and thanks for the explanations it really is an excellent tool.

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

Amazing work !

The article from this video can be updated with the shiny DAX script for calculation groups

Very useful tutorial. Thank you :-)

Great video!!

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?

Just awesome.

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)

@SQLBI

4 жыл бұрын

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! :)

thanks

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.

@SQLBI

3 жыл бұрын

You can already use Analyze in Excel, and it works very well with Calculation Groups!

Thank you so much; If I could, I would put 100K like for this video. GRAZIE MILLE DAVVERO

" we never know..." LOL Great video! Thank's

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?

@marcorusso7472

3 жыл бұрын

Yes, you can apply a filter to the visual or to the page. For Power BI, it is just another table with one column.

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?

@SQLBI

3 жыл бұрын

You don't need calculation groups for that! www.daxpatterns.com/parameter-table/

@nigeltufnel4031

3 жыл бұрын

@@SQLBI I've actually used this pattern before. To change measures I used the switch function. Thanks for the great post and personal response!

Thank you Marco. Really great tool. Just one question, when creating measures in Tabular Editor, I don't see IntelliSense activated? Is this possible?

@SQLBI

3 жыл бұрын

Possibly a future version of Tabular Editor will get IntelliSense - you can find more details here: github.com/otykier/TabularEditor/issues/64

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,

@SQLBI

3 жыл бұрын

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.

Thank you! Is there a chance to sort a matrix by a calculation group item?

@SQLBI

3 жыл бұрын

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...

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

@SQLBI

Жыл бұрын

See www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

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?

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.

@SQLBI

3 жыл бұрын

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/

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?

@cemtekesin9033

3 жыл бұрын

Here, if anyone also wonders: kzread.info/dash/bejne/qodl1pRuj6rZh9o.html

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?

@SQLBI

3 жыл бұрын

I suggest you download the sample file from the article in the link and compare with your solution.

Great video, Marco any idea how I could do market share measure on tabular editor?

@SQLBI

3 жыл бұрын

What do you mean?

@stephenbrincat7124

3 жыл бұрын

@@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%.

@SQLBI

3 жыл бұрын

See this: www.daxpatterns.com/hierarchies/

Hi Marco is this just available in power bi or is it available in tabular models aswell? Thanks

@marcorusso7472

3 жыл бұрын

It is available in Analysis Services 2019 and Azure Analysis Services, too.

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.

@SQLBI

2 жыл бұрын

See this: www.daxpatterns.com/week-related-calculations/

If we will apply % formatting then we will not able to apply conditional icon formatting is there any way ?

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.

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!!

@SQLBI

3 жыл бұрын

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!

Marco, thank you a lot! Could the calculation group be copied to another PBI file?

@SQLBI

3 жыл бұрын

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

@ivanrybalchenko7225

3 жыл бұрын

@@SQLBI Thank you for your answer!

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.

@SQLBI

3 жыл бұрын

You should use the column of the calculation group in the category of the line chart. It should work.

How can I add conditional formatting to my measures in calculated group?

@SQLBI

10 ай бұрын

See www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

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?

@SQLBI

3 жыл бұрын

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/

👏👏👏👏👏

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.....

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?

@SQLBI

3 жыл бұрын

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

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

3 жыл бұрын

You cannot, calculation groups only work over measures.

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?

@SQLBI

3 жыл бұрын

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.

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!!

@nikhil1888

3 жыл бұрын

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.

@SQLBI

3 жыл бұрын

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.

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! ❤

@SQLBI

7 ай бұрын

No it is not possible to change them dynamically.

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 ?

@SQLBI

3 жыл бұрын

Yes, you have some limitation in arranging the position if you combine calculation items and measures in the columns of a matrix.

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

@SQLBI

3 жыл бұрын

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/

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

@SQLBI

3 жыл бұрын

See www.daxpatterns.com/standard-time-related-calculations/

@rajeshnkashyp5100

2 жыл бұрын

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.

@SQLBI

2 жыл бұрын

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.

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?

@SQLBI

3 жыл бұрын

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: kzread.info/dash/bejne/fmF9yMWBm5fdY6w.html

After two years is it now possible to refer to another item in calculation group without running into troubles?

@SQLBI

Жыл бұрын

It's the nature of calculation groups, DAX doesn't have full recursion so no differences.

will this work if i publish it and only have PowerBi Pro - and not Premium ?

@SQLBI

3 жыл бұрын

Yes it works with Power BI Pro, too!

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....

@marcorusso7472

3 жыл бұрын

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.

@nikahafiz1

3 жыл бұрын

​@@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) )

@marcorusso7472

3 жыл бұрын

@@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.

@nikahafiz1

3 жыл бұрын

@@marcorusso7472 tks for the clarification/correction. still learning...

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

@SQLBI

3 жыл бұрын

Calculation groups disable implicit measures - see all the articles in the calculation groups series: www.sqlbi.com/calculation-groups/

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?

@SQLBI

3 жыл бұрын

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!

how do I activate the external tools tab?

@SQLBI

3 жыл бұрын

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).

@pedrosanteloy

3 жыл бұрын

@@SQLBI Thanks a lot! It is now working.

I'm connecting my tabular editor to my Tabular cube, and appears disable, the option create > Calculation Group Alt+7 :-(

@SQLBI

2 жыл бұрын

It depends on your compatibility level, probably you should upgrade your model.

Uau...

I don't see External Tools tab once I install Tabular Editor :(

@danjeon

4 жыл бұрын

Same

@marcorusso7472

4 жыл бұрын

​@@danjeon Make sure you install Power BI Desktop July 2020 first, and then install the latest version of Tabular Editor.

@marcorusso7472

4 жыл бұрын

Make sure you install Power BI Desktop July 2020 first, and then install the latest version of Tabular Editor.

@danjeon

4 жыл бұрын

@@marcorusso7472 It worked as described. Great! Thank you, Dan

@aliramadan7425

4 жыл бұрын

kzread.info/dash/bejne/nIeXy8WPctXYYNY.html

👋👋👋👋

would've be nice if you could paste those formulas instead of just briefly showing them

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

4 жыл бұрын

I've seen the future and... just wait, not too much :)

@1sefirot9

4 жыл бұрын

@@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

4 жыл бұрын

@@1sefirot9 Definitely, it's the beginning of a new era!

@zibroyd

3 жыл бұрын

@@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?

@SQLBI

3 жыл бұрын

Performance and better code organization; ability to customize the format.