REDUCE the # of measures with Calculation Groups In Power BI
Вставка
- Опубліковано 29 вер 2024
- Sometimes you end up with a LOT of measures that are related. You can reduce the number of measures with Calculation Groups in Power BI and clean up your field list!
Download Sample: guyinacu.be/ca...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #CalcGroups #GuyInACube - Наука та технологія
This is so useful is SO MANY WAYS! Having these calculation groups pre-created for my future reports. Thanks Patrick!
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!
So awesome to be able to build this in PowerBI- just like you can in SSAS!
Yes! This has been a long awaited feature.
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!
Hi Patrick, Thanks for always sharing the tips. I feel like I discover gold here when I watch videos from your channel.
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!
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.
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.
This is great. Thanks for creating this video. Helps a lot!
Great video, thanks Patrick.
Is there any way to add conditional formatting to individual columns when using this approach? Thanks.
You inadvertently answered a debate about 'PY' vs 'LY' that has been raging in our department for some time.
Interesting. Curious as to which won and what the points were. :)
Well, I am on the 'PY' side. My argument that 'last' in common parlance is ambiguous. 'last year' and 'in the last year' mean two different things. In one situation it means a 12 month time shift and in the other it's a rolling 12 month. Meanwhile if I can force people into a 'prior'/'previous' then I can help eliminate some of that ambiguity, at least in part.
@@micahdail as you mentioned LY is really a bad choice due to the duality of context - which year is last 2019 or 2020? If you say 2019 is last and 2020 is current then your are not using the "last" based on the actual order (the last in some list), but you rather use the current/previous approach and just name them Inappropriatly as there should never be a moment anyone can even potentially disagree on what that means
@@micahdail ah! awesome! 👊
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
Can you guys update this to the current version of PBI or Fabric?
Can we use Calculation Groups in Excel Data Models that use Excel Data Sources?
Conditional formatting could be a issue?
Patric, this is PBing awesome!!!
Awesome video. Can use these calculation groups in visuals like in charts?
This is awesome. How can you get dynamic measures to format dynamically. e.g. numbers are $k or $m and percentages stay as %. We can do this in the chart visual by selecting "auto" format. Though the matrix doesn't have the auto format option.
Stay tuned for another video on that 👊
This is awesome. Works great in Matrix visual but I am not able to use it correctly in Multi-Row Card as I cannot sort on how they are displayed. DIsplaying alphabetically and there's no way to sort or rename measures in visual
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.
This is amazing!
Aaaaahhhh NOOOOO!!! My mind get down shocked!!! :-D Thanks, it very-very useful for me! 👌👌👌👌👌👌👌
Is there any way to do calculation group without table editor? It is not allowed at work
Super Nice one. Can we use same Time calculated group(Mtd,YTD..) for multiple values (Qty,Sales) on the same matrix ? If yes, can I rename the column names in the matrix? Eg: "Qty MTD" and "Sales MonthToDate"?
WOW Great!!! Finally we have it!
Thanks so much for your videos...top everytime
p.s.: I want "Guy in a cube" sticker for my PC, where i can buy it??!!
Hello Sir, Please help me, I want to import data from apache druid database but not found any options to connectivity. Please suggest me.
when i place time intelligence function it shows me error of relationship ?how can i solve this
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 !
Question: I have created 4 items in a calculation group. I use them in a Matrix on the columns. The columns are order on alphabet. How can I order based on how I created the 4 items?
In tabular editor, click on one of you calculation items. You can change the order of presentation by changing "Ordinal" to reflect your order.
@@Maseatron thx thats works great.
What if I want to have a graph where the columns are one of the items in the calculation group and the lines are other two items?
Can SELECTEDMEASURE() really be qualified as returning a scalar value? I'm curious.
Thanks, this is what I am waiting for! Superb! Just one question, is the individual calculated item formatable by itself? E.g YoY% to % format and MTD is absolute format?
Ohoh, I saw it at the last few second of the video, thanks a lot!!! Too rush asked the question!
haha all good. Glad you saw it in the video 👊
Please, how do you do this WITHOUT tabular editor? $$$
I need to stay more up to date on PBI stuff. I have still been making separate measures and I see I am wasting a lot of time.
How can I use the calculation groups in excel data model?
I've been created some calculation groups in my azure as model and used in pbi reports for some time. I've noticed that if use measures with calculation group on bar chart (or other types of visuals), the measures under visual tooltip section is automatically applied with calculation group function, and I can't use regular measure under tooltip. Therefore, it really limits the usage to only table or matrix visual, is it the limitation by design? Recently, I've been working on a project with pricing data. The user would like to pick which type of price (min, max, avg, volume-weighted avg) to show in a report. At first, i thought i can use calculation group functionality, but I've struggled to get it done, so i created individual measure and use switch function to get it done. Do you think it can be done with calculation group?
Legos and PowerBi..thats what I'm talking about!!!
Nothing better than that! 👊
Can you please make a video explaining how to derive a table, from an imported table set, using queries and forming a graph from that derived table?
Finally I see these calculation groups that caused such a fuss in the community in action :)
Woot! Thanks for watching 👊
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 ??
Am I using Calculation Groups? I AM NOW!
YES! They can be a great option!
THE Calculation Groups is a paid tool now… any free alternative?
I have multiple measures that are exactly the same but the main parameter. How can I reduce the number of measures? In Basic I would use subroutines…
Thanks for any tip
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.
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?
If anyone is interested in how to dynamically format a measure in Power BI here is a nice tutorial:
community.powerbi.com/t5/Desktop/Dynamic-formatting-of-measures-tutorial/td-p/1297373
THIS. IS. 🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌
I was able to reduce the number of measures from 178 to 61 thanks to calculation groups! This is so useful!
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.
WOW, JUST WOW.... EXACTLY WHAT IVE BEEN LOOKING FOR, MADE MY DAY! THANKS PATRICK!!
Love it! Glad it made your day Ricardo! 👊
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 had to create a million measures for me to find this video
This is very informative. Can the calculation group be created by any other external tools like - Dax Studio?
Everytime I try it, my sales figures have no „sum“ characteristic anymore... anyone knows why? This does not work for me :(
Is there any other way to do so, in old version its kind helpfull if it works
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.
Hi...how can we subtract variables in group calculations??
var currmonth=.....
var lastmonth-....
return currmonth-lastmonth
The subtraction is not working ...........
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.
Hello, Patrick. That was nice, thank you. But what about different colour formating for each column in this case?
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 really amazing, Thank you so much Patrick for introducing it.
Bananas.
YES! 👊
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?
as just about everyone else has said..... "WOW!". Thanks Patrick
Appreciate that Spencer! 👊
Hi ,
There is one senario for you ... don't know is it bug or not....may b you can fix it.
Senario-
Different values of calculated clm or measure in power bi desktop and power bi service...
Plz help.
Thanks ,
Shyam
How can I use the name of the metric I've selected in the title of my chart? SELECTEDVALUE doesn't want to work...
What if i don't have measures but columns. And I want MTD, WTD, et al measures based on columns. In that case I won't be able to use SELECTEDMEASURE() function. what's the solution in that case?
I have created a dynamic names for measure like instead of CURRENT QUARTER will show 2022-q1 or q2 dynamically no need to hard code the names of the measures every quarter
It's just me or somebody else also refers to this guy as "PowerBI's Will Smith"?
will this work with non-Time Intelligence time-based calculations? We're on a 4-5-4 Calendar at work, so all the YTD, QTD functions don't work and instead we have to manually to the calculations.
Thank u Patrick, How we can group Rows (not measure) in matrix with calculation group? Many row in matrix is problem too.
I have a column parameter (met & notmet) and row parameter (P1,P2,P3,P4) in power bi matrix. Now I have values for these and a total. I want to add percentage only for met next to total.
When I add percentage it shows for met/ notmet/ total. I don't want that way.bi just want to show percentage for met. Is it possible? How?
this seems awesome....but are people that use POWER BI Report Server supposed to do?? I cannot connect the Desktop (which i guess is not the RS version) to the report server. So i cannot publish anythng. Then there is whole Direct Query, vs Import vs SSAS. All your videos seem to overlook those on SSAS and Power BI Report Server. I get so pysched, then crestfallen as i can't use it your wonderful ideas
This is definitely a game changer for me. Brilliant! Thanks for sharing.
Awesome to hear! 👊
Hi Could you help me on my requirement , 3Yr or 5 Yr or Growth Assumption YOY, all 3 will be one slicer as Radio button. Only one of these would be selected.
If Growth Assumption YOY is selected , then a % for that would be entered in a text box
please provide your email id will send this requirement to id Great help if you can provide the solution
I love the way you explain things, like we were dudes, pals, friends, that's really nice, haha cheers from Brazil
This is incredible but I wish I didn't have to go convince my boss to purchase Tabular Editor to accomplish it.
Argh! each of the columns in my matrix has the same values despite the formulas being built very close to Patrick's. Any suggestions for a likely cause? Time period rows are accurate and the overall total matches a matrix with only the measure (Total Sales).
Although I experienced performance decrease in Power BI Service with a very dense model and Formatting give me headaches :(
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.
This does not work for me. when I create the calculation group, it doesn't create a name column, which is, I presume, why the whole thing fails. What settings should I check?
NOW you tell me! 100 measures later!
Regardless, this is something long needed and will be great to implement. Thanks!
Tabular Editor appears be a trial if not have to pay for it, cannot import tables manually from PBI, so the app is not working for me, I am trying to create a measure filter so that I can swap measures in each visual within the view, do you happen to have a video for this? Thanks dude!
But the calculations were dragged from your measures field? They were already existing.
Good learning. You both are doing a great service by en lighting the people like us.
Hey Nags, your video is pretty helpful too!
Might be a dumb question, but can I only do this in Tabular Editor?
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 have time intelligence measure in 1200 compatibility level. Is there any way to replace my existing measures with time intelligence measures ?
When will we get calculation groups in Power BI desktop?
The calculation group table can have any name. But can the column name be changed? Does it have to be called "Name"?
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?
could you explain how to conditionaly hide or show a column in a matrix regardless of empty columns?
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 a really neat trick that has it's uses but I really wish this channel would spend a minute diving into downsides or "bad use" cases a bit more.
Great Content thanks, it would be great if there where another solution to filter the name, for example for cards, it looks like it will be messier if we have to do a slicer for each card
can they be used in scorecards (or some other visual component), and how?
I don't think this is in the correct thread, but here is what I'm trying to figure out. We are a food bank that takes in donations, and then distributes to food pantries. In the fact table the donations are on on one set of records and the distribution is on different set of records.
How can I find the distribution records when choosing the Donor from a visual?
Bananas!!
Hi, I am unable to download the sample. Is there anywhere else I can get it from?
Can you please tell about the keyboard shortcuts to access dates, month and year only with keybaord.