Let's stop for a second and compare our old world with the new. Why DAX Measures
are so much more powerful than the spaghetti of fragile formulas we're used to
writing in excel. Keep watching, so we have seen a basic set of measures and
there are so many powerful things here, so many powerful things. One of them is
reused, right. Not only do you define your measure once and you can slice and dice
it any which way you want and I mean remember the old way of building reports.
If the person asks you show me sales by category subcategory that's all the
report was capable of and then if they said I was show me some day or show me
by month you had to kind of go back and redo a lot of times. Certainly in a
slightly more complex scenarios maybe not in that one. But in here, you just
define sale once and you can bend it to your will. If you've seen matrix I mean
gosh I've seen it and I can't help but Peter miner that movie I mean you can
bend reality to your will. So it's really really cool you can define your measures
once use them everywhere, not only in any shape of report, but you can reuse them
again to define additional measures, perhaps more complex more awesome
measures. So we have variance variance percentage and of course now I can go
back and look at our Excel. So here, if I wanted to do variance you would in the
old world you would have two separate pivots and these, these are call side
formulas side of the pivot, alright. So you will say yep, show me variance would be, see
so now it's going to be sales minus budgets so e-six
minus I-six, all right. So that's that's a
variance there. Now the results are similar so if you look at accessories
bike racks or is it accessories bike racks variance is 192 for 2016 and
that's the right answer. But look what happened here, for just a simple scenario
we have 17 different formulas, that's right, 17 each one is different, right,
each one is different. How far is it from that define once use everywhere scenario?
We already have 16 formulas and not just that, these are, these are just so fragile
you can, you can break them so easily. You can fat-finger over it, of course
what if you change the shape? I mean what if you bring in subcategory and cat,
well not that. But what if you bring in month over columns?
Now you gotta redo everything all, all over again. But not not in our, in our
case, right. Define once use everywhere once you have variance, you can slice and
dice variance any way you want, right. So that's, that's one and the other one is
slightly subtle and it's not it's not it doesn't appear here but imagine that we
once when we did the budget, we did not have the gaps or the gloves category,
right. So what's going to happen is that gaps and gloves are not going to show up
here. Gaps and gloves don't show up but when we started selling maybe the third
month into the year we came up with gaps and gloves. So it was not budgeted for,
but we started selling it and that happens all the time the one data set
doesn't quite perfectly match the other data set in terms of elements involved,
right. In think, think products, think more granular and then what happens our
beautiful formula is broken because guess what it's doing here, it's
subtracting gap sales with the jerseys budget.
Come on people this is broken, and hey, as much as I love Excel these are the kind
of rough spots which just killed me, alright just just killed me I mean because a lot of
times I wouldn't pick up on the error I mean I would build something with a set
of assumptions, but a small slight change and I didn't even have to change my
report. I mean imagine you didn't change
anything in the report but suddenly in the system data caps and gloves show up
you didn't touch your report. All you were doing was hit refresh maybe but now
your report is broken. Because it was based on all of these fragile formulas.
So guys I want you to take a moment and recognize how big this is. Because hey,
now maybe you're saying hey Avi, I always knew that. But the key thing is that you
got to understand how awesome and epic this is. Because believe me at, at
some point or later you're going to have to try to convince others. So great,
terrific so we're going to stop here, we have talked a lot about measures but
we're going to talk a whole lot more and it's going to be epic.
Hey, keep watching more videos and keep learning Power BI. But if you did enjoy
this video I would love to hear from you. So leave a comment, like, subscribe, all
the good stuff. Power on my friends