Now if you are a power bi beginner or still feel like a beginner then this channel is for you so make sure to subscribe and click that bell so you're notified whenever i answer your power bi.
Question so this question came from sye insider learn power bi family and sye thank you so much for asking this question and thank you for everybody who.
Jumped in to discuss this and get get to an answer especially noel clark you can see this engendered quite a lot of back and forth and a lot of screenshots and dax formulas going back.
And forth i'm actually going to show you a simplified version of that because i like simple but let's look at the question and i'm also going to tell you how you can ask your power.
Actual hours for 2021 right now uh and we don't have a 2022 2023 uh and oops this should be 2024. uh you get the idea and often when members are starting out.
Uh you know i know i come from an excel background and excel has its way of thinking and frankly i felt i'd been using excel for years and i felt really really comfortable in excel i knew my.
Way around i felt pretty confident i could even if i didn't know the answer i could always figure it out but that can sometimes be frustrating in power bi but again if you watch this video you're.
Gonna not only figure out the answer to this but maybe how to change the way you think and and start thinking more like power bi and less like excel and it's going to help you in a big way in the.
Long run but again looking at from excel perspective uh we're trying to do something really simple right so we essentially want to do as you can see here it is hard-coded so let me click on.
Posts Related:
That cell again right so it is dollar b
Dollar two and again when i was working in excel this was yeah i mean i knew it uh pretty quickly right so absolute references but.How do you achieve the same thing in power bi so i promise i i would tell you how you can answer your questions and it's really simple all you have to do is go to learnpowerbi.comquestion.
We're going to put a link in the corner and uh yep it's going to walk you through what's the best way to get your questions answered now by the way if you.
Do want to follow along with this video then you can get this file at learnpowerbi.com download now if you have any trouble with the download don't hesitate in.
Reaching out to me via email at rvsing.com all right so we are going to solve it in power bi of course but for a little bit we're going to stay in excel because i.
Want to hold your hand to take you step by step from that excel thinking to power bi mindset right so again here we're trying to do this absolute reference now of course there's no such.
Thing in power bi uh i don't know if you've heard me talk about it in my main tutorial 60 minute tutorial if you haven't seen that definitely check it out but one concept that we talked about.
Is when we're creating measures formulas in power bi they operate as an island they can't reference this one there's no way but they can reference other measures right so again if you kind of.
Get that then you're gonna see how the solution comes together so again excel thinking absolute reference in power bi the first thing that i'm gonna do shift for you hopefully is that i'm gonna just.
Add this as a column here right so
It's still an absolute reference and we're gonna we're gonna show you how to do that but again this is how i think like when i'm.Thinking in power bi so i'm like can i do a column now of course i say column here but i'm thinking a measure in power bi and again measures are simply power bi formulas we gave them a cool name.
Because they are pretty cool and awesome all right so yeah we call them measures they're like formulas but way more awesome all right so um so right so so again and it's the same stuff right so.
We just hard coded this reference here but now it's in a column and our formulas can still reference to it but you notice what changed here right now this is a big change this is where i.
Want you to pay attention right so notice that now it's not an absolute reference anymore it's simply referencing another column this is a column name and where we can have column.
Name we can have measure names all right so now we're over in power bi and i've kind of recreated the same data in the same table just a quick word on the data now i'm.
Using a very simplified version of this data now we have the year and then we have the actual r's and the forecasted hours and then i did add a column here kind of plant location just to make it.
More interesting now of course real data might look quite different now of course this doesn't have to be ours this can be finances dollars sales budget whatever right so it could be anything which is.
Actual forecasted and maybe budgeted uh and real data would have more complexity it's quite possible that your actual table might be different it might be more granular so in this case actual.
Table is at a daily level and the forecast might be at a monthly level and the actual might have a lot more detail specific product like to the uh and and.
The forecast might be at a high level but for the most part for this discussion that we're going to simplify that and use a.
Simple solution and that would still apply even if your data set has those nuances all right so let's keep going so again if we go back to what we.
Had done in excel we had try to create this column kind of this fixed column now we can't quite do b b dollar b dollar too but i'm going to show you how you can do the equivalent so you've seen.
The data set and again we brought it into uh into power bi and defined some really simple measures we have our total actual hours which is.
Just the sum of the actual r's and we have total forecast stars which is just the sum of forecasted hours and again we have kind of replicated the starting point that we had in excel.
So and now we're going to do this next step which is the absolute reference we've been talking about so for this we're going to define another measure so i'm going to go to measure tools and.
Click on new measure here and here i'm gonna say total actual r's now i wasn't really sure what to call it maybe it's fixed maybe it's baseline uh.
Or maybe you wanna really hard code and say it is 2021. i'm not really familiar with what's going on uh but i hope you noticed that i do think quite.
A lot about what to name my measures yeah that's one of my obsessions they got to be named right all right so we'll go with total actual hours fixed again i'm kind of debating between.
Baseline but total xlr is fixed uh and we're gonna you bring out our magic wand so calculate is a magic wand because it lets us alter the filter context now if you have no idea what.
Filter context is or only have a fuzzy idea then definitely make sure to go through my 60 minute tutorial or even better my full power bi training.
Program so calculate the magic wand and what are we calculating well we are calculating total actual r's right so that's that's uh that's what we're calculating and i.
Love reusing my measures but we just want to shift the filter context and what we want to say is we want to have a calendar year.
Let me make it a little bit smaller there we go and equals 21. there you go that's the equivalent of hard-coding it let's see how this works.
And for that measure i'm gonna add add that here and i'm just gonna change the order just so we have oops we have things the way we were.
Doing it in excel so notice what we have what we had done in excel we had taken this and then we had kind of hard coded it b2 right to hard coding fixed reference and we did something.
Similar in here and now you notice how easy it becomes and that's what i love about power bi and dax and dax measure is like once you it's like lego blocks you build one.
Small lego block and the next level block is super easy right so we're going to play with some lego blocks here i'll add some more basic measures with that basic math for variance and.
Variance percentage and i'll be right back all right my friends so again kind of like lego blocks so we i love reusing my measures so we already have the forecasted ours actual rs measures i.