Analyzing Excel is one of the coolest and valuable Power BI
features which lets you connect an Excel Pivot table to a Power BI data
model but this is where Implicit and Explicit measures work quite differently. Let me show you why
that is important.
Keep watching
to show you the next key difference between
Implicit and Explicit measures I have to go a little bit deeper so stay with me here what I have on the left Is a
model which is using Implicit measures.
That means that I have simply you know dragged and dropped fields from my
table an existing column I just drop it here and then I chose the sum
summarization. I did the same with Budget Amount I didn't explicitly
define any measures. This is being summed and I could change that so that's an Implicit Measure and on the right I'm using
Explicit Measures so this Is I've defined a measure in
sales you can see I've used the definition here same thing with budget now what you would notice is that
they both return the exact same results. They look alike but I want to take you to do this
visual
so think about the purpose Is not so you can use this model the purpose Is so that you can publish it and
this becomes a single source of truth from for all reporting and you want
users or your self later on to be able to connect to it using the tool of their choice and
I don't know about you but one of my favorite tools Is right here and
that's what I want to talk about that how it differs when you have
Implicit versus Explicit measures so I'm gonna speed this part up but what I'm doing essentially is I'm uploading
both models to PowerBI.com I'm publishing them and then
we're going to see how it impacts when you connect using Itself.
All right so we have both
models uploaded there I have the implicit model and I have the explicit model and of course you can
create a Power BI Reports on here but remember, we want to be able to connect
perhaps different tools and certainly Excel is one of them so that's what I wanna zero in on so what I'm gonna do is first try the explicit one and we're going to go in There and Say Analyze in Excel
[Alright] so it's going to ask me to save this file open it
So it gives me a Pivot table
connected to the data model in the cloud right so that's what analyzing Excel gives me and
I should be able to create a report here. Now I have
all the field lists available here There is one difference if you
would notice that my measures are
bubbled up top so Budget and Sales Measures are bubbled up top and the tables are still down below so all the column attributes
Budget, Calendar, Customer and if you keep going down this product and there's sales so all of that stuff is there but the
measures are up top which Actually I kind, of like I love my measures give me my measures, well yeah all right so we can
We can
build something like this let's make it giant shall we
and and we can slice and dice it any which way, we want so
we can say hey maybe show that to me by
category and subcategory so you get the idea and this becomes my report which is connected to that.
All right so what we're doing again is
that we're trying to have behavior one source of food and we're trying to create excel reports connected all of that now what if
we had not defined those measures explicitly right so we had the other data set which had
we had just used implicit measures which would really you know you dragging on a column and
choosing how to summarize it some as an average and so forth so that was our first model and
now let's try the same thing with that so, we're going to say click analyze in Excel
All right.
Open that and perfect. We get a Pivot table connected remodel Whoo-Hoo
but wait
something seems off okay so we didn't have any measures so they didn't bubble to the top that makes sense
no big deal you know I should be able to just
grab my sales and budget amount and just
drag it in there right so yeah I'm gonna grab my sales amount column and I'm just gonna put in the values.
Wait it's not letting me oh, no
bummer what about budget amount maybe they'll be cooperative budget amount
no dime
so this Is
Gosh I live in long terms right now maybe you're not going to be concerned about this maybe you're not thinking about this but my
friend you have yo have that vision you're not just building this model for yourself
you're building it so that it can become the one source of truth and later you can connect any and every report to it
Power BI Dashboard Report Excel so because this one scenario is severely hampered if you do not have
explicitly defined measures and later you connect an Excel file to your Power BI, publish, model there's really nothing you can
do there's no math you can do them and you can
list out the budget amount because that's that's not what I want to do I
want to be able to use my measures and slice and dice It so I'll rest
my case there I hope I've convinced you. I'm certainly pretty passionate about this and and certainly would encourage you hey
just just do the right thing. Just define your measures rather than
dragging columns and using the inbuilt Summarization in Power BI Desktop.
It's going to be good in the Long run when you connect eventually Excel reports to it but it's
also good in the short run because it helps to reuse measures as you'll see as well
and gives you more control, over the measures
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!