How to use layouts for company templates with Power BI | Community Webinar


>>Hi, all. Good morning
or good evening, depending on where you’re located. Thank you for joining
our Power BI webinar, and my name is Deepak Shankar. I’m a Community Manager within the Microsoft Business
Application group. Today, we have Mike, an MVP with us, who’s going to walk us through the new Power BI features. Mike, it’s all yours now.>>Hey, again. It’s Mike. I’m the owner of Power BI.Tips. Today, we’re going to
walk through just working smarter and using some layouts. These are some ideas of Power BI template files that I created
off of my website Power BI.Tips. So I’ll give you a quick introduction of who
I am and what I’m all about, as long as I can invite
my slides. There we go. So my name is Mike Carlo. I’m a Principal Data and AI
Architect Independent Consultant. I’m one of the owners
of Power BI.Tips. Myself and Seth Bauer
run the website. You can catch me on LinkedIn, or if you want to
catch me on Twitter, or on YouTube as well. I have a number of videos
there going through techniques and tips and tricks
specifically around Power BI. So kind of giving you
some background for this talk and what we’re
going to go through today. I would call myself, in some ways, I’d say
I’m a lazy engineer. What I mean by this is, my background is
mechanical engineering. I have an MBA, and I’m working through my Master’s in
Data Science currently, but I always kept trying to find the process or pattern that would produce the least
amount of effort for me. So there’s two core concepts that
we’re going to talk about today. We’re going to get to this
concept or this idea of a layout. Basically, it’s like a template
file that we enhance and make it prepared for publication
within our reporting, basically connecting to the
cubes inside PowerBi.com. So as I’ve been using the Power BI
tool for a number of years now, I’ve come to the realization that I want to do things
in an efficient way. So this brings up the pattern
of how we publish reports. So our current pattern, and what most people
are familiar with, and especially when I do consulting, this seems to be the
most common pattern. The idea here is,
I’m a report author. I will make many reports here. So this is me in my
left-hand side here. I will build many different reports. Then for each of those reports, I’ll connect to some data sources. It’ll maybe be Excel. It’ll maybe be in a SQL Server. We’ll mash up that data, and then I will publish
directly from Power BI desktop. I’ll push that report
out into the service. So this is me publishing these
reports directly into service, and now I have this
nice, pretty report that people can consume and go
get from my workspace. The challenge of this is, every time you publish this way, you have this repeated effort where each report comes with a
corresponding datasets. So that’s what I’m showing you
here on the right-hand side. So I have two reports, and for those two reports, I have an equivalent dataset
for each one of those. So this works well in a
business user standpoint. It’s very easy to manage. I know there’s a one-to-one
relationship between my reports and my datasets. But as you start scaling this larger, as you start looking to
bigger organizations, this sometimes breaks apart. We have this concept, or
maybe had a challenge, maybe more along, not a concept, but you have this same
challenge that you do in Excel. People will bring in a
bunch of information. They’ll manage the data in Excel, and then they’ll e-mail that file or that Excel file across to other
people in the organization. So now you start
versioning your files, and now it’s V1, V2 at
the end of the file name. You can get this really disparate environment
where you have just many, many different files
all over the place. So what we want to start doing is, we want to start
taking some cues from the IT organization and start leveraging some of the
patterns that they use. So a common pattern that an IT group would use
to publish would be me, the data owner, would then mash
up a bunch of data sources. We would bring them together, and then we would push them
into some analysis cube. That cube may live on-prem. That cube may live in the Cloud. I’m leaving out a lot of
technical pieces here. We’re just really going after
the high-level concepts here. So the idea here being is one analysis services model should be able to serve
many different reports. So for each of those reports, we’re looking at the same dataset. We have the same data model. Our definitions or
measures that we’re using are all centralized in that model that has been produced
for everyone to consume. So this is really interesting. So now we’re able to start
building one larger, more capable model, and then we have many different
reports that we can publish. Then we would publish these reports
into PowerBi.com, the service, and then all of our reports
would then be able to refresh themselves back
to the analysis services, but the analysis services instance
is managed in an IT space. So I like this approach. I think this is very efficient
from a standpoint of we can have a very smart people working on the data model and building
the capabilities there. This allows for enterprise calculations or
enterprise-based figures. So we know how these calculations or these business rules are
being applied to our data, and it’s all managed in
one central location. So if we find there is a mistake, or we need to refresh some data, we’re able to go back to the
singular model and update this, and then this model be able
to get more information. We can add more tables or more
measures, and then automatically, all of those measures
and calculations would be pushed down to each of the reports that we could then
update and then republish. So this is really an IT
pattern that we find. So let’s blend these two approaches. So we need to move fast, and this is where I’m going to coin a phrase here
that I like to use. It’s called “Act like the business, but think like IT”. So my background is really in
the business side of things, where I really came
from that Excel world. I love the Power Query. I like being able to mash up my data. I like being able to
see the data coming in. For me as a business
user, essentially, I wanted to have as much and as raw of the data
as I possibly could, but I needed tooling to
help me accomplish that. So this concept, and I’m
going to present to you maybe a different pattern here of how we think about maybe
publishing reports now. So we’re still going to use
the analysis services cube, but we’re going to use
the one that’s dedicated and built into PowerBi.com. The idea here is, me, the user, would then build out a dataset, and it could be in Power BI desktop. You could use other tools as well. I think Tabular Editor is
another good tool out there that you can modify and
make datasets with, and you can modify or read datasets
that are in the Cloud already. So the idea here is, I’m going to be
building out a dataset, and from the build of that dataset, I would then publish it. There’s really not a
lot of report pages. So yes, there’ll be
report pages with it, but there’s really no information. I’m not going to put visuals. I’m
not going to put graphs there. I’m pretty much just going to
publish a dataset as it is. Now what I’m going to do is, when
I start a new Power BI file, I would go directly back to the PowerBi.com service and
live connect to a dataset. Then I could build many different
reports off of the same cube. So this really mimics what the IT group is really looking
to try accomplish here. We want maximum reusability
on our data models. We want to have one dataset that
can serve many different reports. That’s just more efficient. We have one definition
of what sales is, or you’re not trying to manage multiple measures between
many different data models. Then now you can have
all these reports published again back
down to PowerBi.com. Then you can schedule the
data source to refresh, and then automatically, all of your reports will
subsequently refresh as well. So quick summary, when we
talk about this concept, there are two opposing opinions of why we’re doing things the
way we do. The business needs. I need speed. I need
to develop quickly. I need to have reports
that are evolving. I don’t know if I know all
the requirements upfront. I just need to put some data
down and get things moving. As I used the report, there’ll be more business
requirements later on, and I’ll have to evolve that
report into something better. There’s usually a wide range of skill levels that we talk about when we’re talking
about the business. There are some individuals who want just to produce the report for you. There’s also, in the business, people who are very technical. They’re very heavily in Excel. They’re writing their
own macros and doing very custom things and
very code-based things. They just don’t have the
traditional IT background that an IT organization would have. Usually, the business is all
around delivering quick wins. I don’t need to have this
massive data warehouse always curated and built
before I can build a report. Sometimes, I need to produce a report in a couple of hours
or a couple of days. So we have to deliver these
quick wins repeatedly. From the IT side of things, we take a little bit of
a different approach. In the IT world, we want to look for a
maintainable solution. So things like
reusability and building a model that I can reuse for many
different reports makes sense. I have a limited amount of
resources as the IT organization, and I want to make one bigger model that many different
reports can serve. That way, I’m maintaining
one source of the truth and getting all this added value from additional reports
coming off of that cube. There’s a lot of things here
around enterprise standards. So enterprises, particularly
IT organizations, have a review process
and going through these different models and how to make sure that
everything is sustainable, and it can be reused, and it’s a repeatable process. So when something breaks,
we know how to roll back the model and go back
to a previous version. So this is going to be
our moniker that we’re using the context for these layouts. So the idea here is, we’re going to walk through this
concept of producing a dataset, moving the dataset into PowerBi.com, and then from that, we have
reports that we’re going to use. The reports that we’re going to use are essentially going to be layouts. They’re going to be
pre-designed reports that have some features or
functionality pre-built into them. Then we’re going to
connect to our cube and start building visuals
inside the reports. Then we can publish those
reports out to the surface. Okay. So we’re about
to jump into demos. I’m not sure how many
people we have online. So I will take a quick pause here and see if there are any questions
that we have at this point. Any questions right now, or should we keep on cruising? All right. I’m not hearing
any questions from Deepak. All right. Let’s keep on going. So let’s start with loading
up a data source here. So I have one file
here on my desktop. Let’s make sure that
I have it up already. I do actually have it opened. Go here to this data source. So this may be the activity where I’m going to build
out a data source. So in here, we would have
loaded a number of tables. These tables might have some sort
of relationships between them. Inside each of these tables, we may produce columns, or measures, or other things in
here that we want to review. Now, something that’s
interesting here is when I’m building out a dataset, I’m probably going to be putting
a couple tables on a chart. I mean, this is usually where I
do some kind of double checks. I may bring in category
here into the page, and I may want to just
verify that, yes, these categories do make sense, like my data is loading properly. I don’t have to delete this page
when we publish this dataset. This is more around making
sure that the data is correct and vetting the
numbers that they make sense. So I may bring also in here category. We may bring in sales amount. I may look at these
numbers and compare this against my database to make sure that the transformations
that I’m applying in this dataset have things
that are related here. Some other techniques I like to use, particularly around
making datasets here is, inside the Sales table, we have a number of measures. Let me see if I have my ZoomIt. Let’s try that again. There we go. Let me
check and zoom in here. So we can go over here and look
at some of these measures. So the idea here is we have
some of these measures, and these are going to
live in the data model. This is going to be that global data model piece
that we’re talking about. So one of the techniques
I like to use here is, even though these are measures
here in the sales table, we probably want to group
them in some way so we can see them in a folder. You can do this by going over to the “Model” button on the
right-hand side, “Model” button. So I’m going to use this one here. This is the “Model” button here. If we click on this, we can then see these are the tables
that we’re working with. We have properties and fields
over here for the dataset. So we’re going to shape
some of the dataset here slightly so we can make
some grouping here. What I can do here is I can click
on “I measure” and we can write the name and we can write
descriptions or some synonyms. This is for the linguistics
model within inside Power BI to help you use Q&A, and we can also add a display folder. So this is what I like using. So what I would like to
do here is we’re going to just call this global measures, and it will add a folder here. Oops, it moved away. So now I have this global measures. Now, another technique I like to use, see it dropped this folder in
the middle of my table here. Now, I’m not too pleased with that. I would maybe prefer that this global measures
move to the very top. So in order to do that, we can go back over here. We can add an underscore
underneath global measures. Now when I hit “Enter” again, you can see that the
global measure has moved all the way at the
top of the list now, and now my measure that I have, a report link is now in there. So now what I can do is I
can actually drag and drop other measures directly
into that folder. So I’m going to organize these, and you’ll see why we
do this in a second when we start building
reports on top of this. So I’m going to grab some
sparkline information. Oops, I think I missed it. Sparkline will add him there. Here’s a couple other
measures down here. I’m going to use Control and
click these other measures. So you Control clicking them, and then I can bring all
of those measures again, up to the global measures table. So now I have all the
measures grouped here, and everything else I have
is almost dimensional or just straight
non-number-based columns. Another thing I’ll point out here, and this is another
technique that I like to use when I’m building things, there are certain number of
columns here that would make sense to be aggregated and certain
things that would not make sense. So one example here
is the product ID. Product ID is an ID of the product which just
happens to be a number. So for building things
on a report and we’re trying to build this for reusability, two things may be evident here. One is we may never
want people to put product ID on a table;
we would then hide it. The other thing here is this
summation sign next to it, means that this sum sign is a number and it would automatically do an implicit
measure against this. So what we want to do is
we want to modify that, and we’re going to go
over to “Column tools”. So for those who have
used this previously, this month, Microsoft has
released the new ribbon. So in order to modify columns, you now have a new ribbon named
here called “Column tools”. This is where we adjust
how things are aggregated. So the default
summarization is “Count”, we’re going to just change
that to “Don’t summarize”. What that’ll do is it’ll
take away the sum sign, and so now it won’t automatically add those numbers
together when we put them on a list or a
table. So let’s see here. Product labels, probably we would
not want to sum them either. Sales amount looks like it’s good. Store key, that looks like
something else we would not want to summarize. Then units, maybe something
we want to summarize. Another technique I
like to use here is when we have areas or measures within our table sales amount units and
maybe the NSAT number or score, if we’re trying to do
aggregations around these pieces, if you want to use this
model both from Power BI and connect to the same live
connection with Excel, Excel does not take
implicit measures. So this column, even
though it’s a number, will not be able to be used in
a pivot table inside Excel. So another technique I like
to use is to build out sums or simple sums around
each of these values. So that way, I have them and
they’re already in my sales table. So I’m going to right-click on the “Sales” table. We’re
going to say “New measure”. We will then say Total NSAT, and then we’ll just do our simple
sum here of the NSAT score, and you’ll see it autopopulates the sales table and then the
NSAT score there as well. So we’ll do that and hit “Enter”. We now have our total NSAT. Now what I’ll do is I’ll actually
hide the NSAT score itself. So instead of using the
implied measure here where I could change this NSAT from, so I’ll drag it here on the page, it’s implied here that
it will do a sum. So the automatic implied
aggregation is a sum. I can change it here by clicking
on this little “Down arrow”. But what we’re doing
with this measure is we’re explicitly calling off
what we want the math to do. So that’s why we’re doing that, and we’re going to just
hide this for now. So I will hide that column, and that way it’s not
visible to the end user. Same thing for sales amount. I’m going to build a new
measure here for sales amount, and we’re going to do the
same total sales amount, and then we will again do
our sum of sales amount. Here we go. Enter on that one. I will then hide this column, and then we’ll do the final thing
here for the units as well. So one more measure for that one. Total units equals the sum of units, and we’re going to use
that from the sales table. So again, these are just
generic good practice or modeling techniques. I’m going to hide this one here. Then finally, we’re going to
need to do now that we’ve made these three new calculations, we will want to move them back
into our global measures table. So clicking back over
here on the “Model” view, we’re going to go down
and we’re going to grab our three values here and we
will drag them up, hopefully. Come on. It’s going to make it hard for me to
drag all the way up there. Come on, scroll up,
little guy. All right. Another way you can do
this is we can actually use or borrow the name that’s
previously measured here. So we’re going to go copy this one because it’s not
letting me drag those up there. I’m going to grab global measures, and we’re going to
go back down here to the very bottom for
these three items, and then we will enter the
global measures in that table. So that should then move all of those measures back up
to our global measures. So now we’ve made all
the measures and we’ve hidden the columns
that are numerical, so we don’t need them at this point. This is the model that
we’re looking at. We have a couple of categories. I have reoriented at the tables
and how they are filtering. Again, another very basic modeling technique when
you’re looking at the model. I typically put the dimensions at the top and the facts
towards the bottom. Then as the dimensions
modify the table below, the direction of filter
goes from the top-down. Okay. So that’s enough for
the modeling piece here. I think we understand what’s
happening and we’re ready to publish this into the
PowerBi.com service. So now what I can do
is hit “Publish” here. It will ask me to save
my data source changes, and then it’s going to ask me where it wants me to publish this stuff. So I will publish this into
my Power BI demo site. Publishing away. After a second here, it should load and
there it’s a success. So we should be able to open the PBIX inside this source. So I’m
going to click on that. It’ll then bring me into PowerBi.com, and you’ll see that we
have our report page here, which really has nothing in it, which is okay, that’s what
we designed it to do. If we go back to this workspace
and we look at what is in here, we have our dataset called data
source, which is this report. Now, if I click on the reports, we also have this report
called Data Source. Now technically, I don’t
want people to use that. So I’m actually going to
trash that and get rid of the report because we
don’t need that anymore. Now we have our dataset here
called Data Source. So great. We’ve already been able to
publish a data source to this location. All right. Now that we’ve done the
data modeling piece, now let’s talk about
the layouts piece. So we’ve made a model, we’ve published it to the service, now we’re going to come back
and we’re going to open up one of these layouts and
see what that gives us. So I’m going to close
out of the dataset, the data source, we’re
going to get rid of that. So this is my data source. We have already produced
that and that is out. So I have three different
layouts in front of me here. If you want to go get more of these, you can actually find them
over at PowerBi.tips. So I will just quickly show you that. Here is our layout section, and we have I think over 15 or 14 different
layouts already so far. So feel free. These guys are free. You can go download them yourself. There are different
types of these layouts, there’s different formatting,
different colors. The idea is these are just areas where you can get started
with these reports. So I’m going to show you
how to work with these, and then we’ll show you how
it looks in the report. Let’s start here with
this one called sunset. So if we’re going to
open up this PBIT file, so this is a template file that
you get from the download, I’ve already pre-downloaded
these templates. So if I open the template file, you’ll see that it opens
up Power BI desktop and the first thing you’ll notice is there’s already basically
a pre-built report in here. It will have a background color, and it’ll have some
blank visuals inside it, and we have some
background areas here. So this one has this
sunset theme color here. If you click on the different pages, there’s already a
multiple pages here. So there’s a yellow
page, an orange page, pink page, and the purple page. So interestingly enough here, there’s already some
controls that have already been built on this report. So this is a template file. If you look over here
in the field’s area, the field’s well, there
is no data source here. There are no tables, there is nothing
connected to this model. So this is why we wanted
to build that data source. This layout essentially here already has some pre-canned or
pre-designed features in it. So if I hold the Ctrl key, there’s already some
navigation elements here. You can actually navigate to each of these different colors by page. So if I click on the yellow one here, you can see that it navigates
me over to the yellow page. Then once I’m at the yellow page, I can then click the yellow box again and it will navigate me
back to the overview page. So to show you how that is built
over here to the “View” ribbon, we’ll turn on the
bookmarks and you can see here there had been bookmarks
made for each of these pages. So there’s a bookmark
that navigates you to the overview page and then it
navigates you to the yellow page, to the orange page, pink page, and then the purple page. So you can jump between these
pages with the bookmarks. The shapes over here
are a simple shape. It’s just a clear shape
with no formatting on it, and then it has an action attached
to that shape and it’s just triggering a bookmark and then taking me to the bookmark
called the yellow page. So what’s really doing here is I have shapes overlaying over
top of these visuals, and as I click them, it just navigates me somewhere
within the report. So this is a feature that
you would build into one of your layouts if you’re
going to make your own as you would want to think about, where does the navigation
element sit within my page, where am I going to put
them, and how am I going to have users navigate
through my report? For this case, we
click the yellow box, it takes me to the yellow page. If I click it again, it
returns me back home. So a very simple interaction, but you do want to think about, especially when you’re
building reports, is you want consistent spaces on your page where you have these
are the navigation elements, or this is where the visuals will go. To also expand a
little bit more here, what’s going on with this background? So this background image
is just an image that I think I built this
in Adobe Illustrator. If you click the paint roller here, you can see the page background. What I can do is, I
can adjust or you can see that it’s just an image
attached to this page background. I think if I change the
transparency, yeah, there you go. So you can see what I’ve done there. It’s just an image that’s
been fit to the page. Okay. So that’s really
what’s happening. Then for the yellow page, I have a different image. So this is a different
background image, where I’ve washed out the colors of the other three elements and left the strong yellow color at the front. Let’s go back over the overview page. So now that we understand
how this layout works, let’s go get some data and
let’s start building a report. So here’s the part that I call
myself the lazy engineer for is, because now that I have
this template built, I can go bring in any
data source that I want and start making reports and
pages in here fairly quickly. Now, I don’t have to build any
of these navigation elements. I build it once and then continue to reuse this
report over and over again. So we’re going to go here
to the “Home” ribbon. We’re going to go to the “Get data” feature up in the upper ribbon bar. When I click this, it will ask me for a number of different options. I have Excel, I have
Power BI datasets, this is from PowerBi.com. I can also go here to
Power BI dataflows. So we’re going to use
the Power BI datasets, but there’s a number of other
sources that are quick links here. If I bring in a dataflow, just for clarity and for
people to understand, the dataflow is simply
bringing in just tables. I mean, you can bring in
relationships with dataflows, but, typically, dataflows are just a bunch of tables
you’re bringing in. The datasets is more like bringing
in an analysis services cube, where I have the relationships and
the measures already pre-built. So I’m going to click
on “Power BI datasets”.>>I’m going to check
my time here real quick, see how far
we’re getting there. Well, maybe I won’t check
the time. Okay, we’re good. Plenty of time. All right. So I’m going to go
look for my datasets. So I have I think what’s called
Data Source. There we go. So if I type in the box
up here “data source”, we can see here’s the name
of my data source called appropriately Data Source and I put it in the SharePoint
Power BI demo site, and we did that about
six minutes ago. So this is what I’m
looking to connect to. So I’ll go connect
to this data source, and we can see instantly
right away we have all of our tables built
out with measures, and then if I open up
the Sales table, boom. There’s my global measures and
here’s all my dimensional pieces. It looks like I might
have missed one here. I didn’t hide this Sales Size
Sort as a numerical column. But in here, now I
have all my measures. So now I can start building
things against this. So let’s just say we need to modify
this layout a little bit more. One of the things I would say, especially when you’re modifying
layouts or taking them from the website that we produce
and how do you modify them, your two best friends will be the Bookmarks window or the pane
and there’s the Selection pane. So these are all the different items. To find those, so this is the Bookmarks one and
I can cancel it here, to turn those on, you’ll go to the “View” ribbon, you’ll go down to the
“Show panes” area, and you’ll show the “Bookmark” pane, and then the “Selection” pane. These are the ones that typically has all the elements that
are on the page. Again, this makes it really easy when you want to
select different items. I’m finding myself, especially
as I’m working with very complex reports and a lot of
different elements on the page, definitely using the
grouping feature. So if you haven’t used that, go Google it. It’s awesome. You’ll really, really like it. Then, as I select different
elements across the page, I do a really good job
of trying to name them. So for example here, I have this yellow clicker. The yellow clicker, orange, pink, and purple clickers, these are my little
shapes that are on top of my visuals on the left-hand side, and then I have these cards. So these are numerical values. Then below each of the
cards, I have a title. So if I click on the title here, I can then modify this
name to something else. We’ll just call it right now
for this one will be page one, and then I can click on the orange title and then we
can rename this one as well. So if I use the Selection panel, it makes it much easier for me to navigate to different
elements on the page. Again, proper naming of your elements on the
page is super helpful. Because then when you’re
going back through this, you can then quickly
see what you’re using to be able to select different
components on the page. So it’s easy to rename those items and we renamed
those different pages, and now maybe we want to add some calculations to
each of these colors. So let’s do that. For page one, let’s just make sure we understand what’s
on the categories we have here. So before I start modifying and adding things to these
existing visuals, let’s just grab a
category column here and it should connect to
my analysis services cube. There you go, and here is my table. Man, there’s so many styling tricks and techniques here I like to use. So this one has a transparent
background on it. So because I’m working with a background layer here
and this is a table, I often want to put backgrounds
on my visuals to make it very clearly stand out
and a nice term border. So I’ll put a background
on this one and then I’ll turn the transparency
all the way to zero, and now I can see I have
my little table here. It’s nice and white, and then it
will look good on my page when I adjust and add other visual
elements to the page as well. So I can click this and then I
can bump it around with my keys, and there we go. All right. So we have different
categories and maybe some of these categories are the data
points we want to put on this page. So I can go over here to category. I can then grab our total units and also add it to my
chart, and there we go. You can see which ones are here. Let’s sort these by total units. Okay, it looks like
computers, cameras, and home appliances are my top three. Let’s add those as numerical
values to these different cards. So now what we can do is we can
go over to the yellow card, this is the data card, and then we’re going to grab in the total units measure to this one. I apparently have a filter that’s already out here.
Let’s get rid of that. There’s my 90,000 for that one, but you’ll notice 90,000
is the total value. So let me zoom in here so
you can see what’s going on. So I’ve dropped the one item. It’s actually aggregating
all the values. So if I want this first page
to talk about computers, I’ll need to automatically
filter this card here. So what I’ll do here
now is I will go to the yellow card and then we
will grab our categories, and we will add them to the card. Oops, I think I dropped my card. Let’s go back to the yellow card, let’s grab category and add it
as a filter. So now here we go. There’s my category
and we want to use computers for number 1
item, and there we go. So now it’s saying the right value. So now I can go back to
my title here and then I can rename this; Computers. So we’ve just made our first
card based on some of our data. We could go through and modify additional pieces here
within this report as well. So I could go back
here to the next one. We can go to the orange card. We can go to the orange card and
we can also add our units again, total units as a field, and then we can then
bring our category over here to the filters again. Now we want to pick
cameras and camcorders. We’ll check that one and that
we have a number for that, and then we can go back to our title and we can
relabel this one as well. I’ll just title this one Camera. Oops, cameras. That’s also another little trick. If you delete something fully, the formatting goes back to normal. So what you saw me do there is
when I deleted the cameras, as soon as I deleted
the entire statement, the formatting went back to black
text and it wasn’t the same. So I use the Ctrl + Z button
to keep the formatting, and then I was able to Ctrl + A, select the entire cell, and then type in what I wanted. Oops, I should’ve probably
named that Cameras instead of just Camera.
Let’s just fix that. It’s going to bug me. So Cameras. All right. Let’s just
do one more just to prove the point a little bit more. We’ll go here to the pink card. Yeah. That’s a lot of
stuff I was testing. We’ll go here to the
total units again, we’ll add that to the card, our value shows up, well then grab the category
and add it to the filters, and then now we will use
our third highest item it would be home appliances. So we’ll click that there, and now our value is adjusted. Go back over to the title, and then now we can say this will
be Ctrl + A, Home Appliances. There we go. So now we’ve
been able to build our page. Now if I hold Ctrl + click here, we could then build the same
thing here on this next page. So then I could rebuild
out these other elements here on this page and continue on. Other things that we can do on
this page or on these pages, we can also build out other visuals. So if I wanted to see some
information by country, we could bring in our total
units by country now. Then as I grab this chart figure, we can then try and
fit this on the page. We maybe want to add some other
things on the report here. We may want to add a
line chart somewhere. Maybe let’s get rid of
the axis for this one. Do we have any kind
of time-bound data? Let’s not do a line chart.
Let’s do a bar chart, and we can do it by SalesOrderID. Well, it looks like that’s
all pretty consistent. Let’s not use that one. Let’s just do
ProductName. There we go. Maybe that’s a list
of stuff. All right. Anyways, the concept here is you can go through and you can
build out things that you want. You can even add slicers to the page, but the idea here is as you build
various elements on the page, it will just look good. You’ll have this
pre-designed background that’s going to help you speed
your report development. So then all I’m doing really is adding the certain
visuals that I want, building the interactions
that I need so that then adjust my report page, and then all my measures
are being built here against the data source model. Okay. One other thought around this. If we need to adjust this model or we want to have some
local measures here, so there’s this concept of a global
measure and a local measure. So because I have live
connected between my report here and the report that
I’ve published at PowerBi.com, I can introduce this local measure. So if I right-click here, I can actually click a new measure. What this will do is it
will actually build me another measure inside
just this report. So this one would not be one
that we would put in the global, other reports won’t
have access to this. However, this may be something
specific for a particular visual. So maybe you want to do
a Count or something. So let’s go. Maybe you’d like a product count, and maybe that’s something specific
for a visual that you want. This time you want to
use a Count function, and let’s try and go after. So start typing. Maybe it’s your ProductName,
that’s what I’m looking for. ProductName. There we go. Sales table with the ProductName. So then I can count
how many products or how many different products I have
named by some sort of grouping. So then now I can reuse that. So I can shrink this
chart up a little bit, and maybe we want to
use our new measure. So we’ll build a new table here, and we will use our
Product Count here, and we may want to
count that by country. So now we’ve been able
to make a new table. Let’s rearrange this. Another thing here you’ll
notice is the formatting of this visual may be
different than other visuals. So if I go back to this one and
turn this into a table again, so you have this nice
white background, but this one does not have that. Hopefully, it’s in this one. You have the “Format painter”. So if I select the visual that
I elect the formatting for, I click the “Format painter”
and then click the new visual, it should automatically pre-apply those formatting elements
to the other visual. So again, this is another
kind of trick that I like to use in order to make the visuals
aligned pretty well here. So now I have another table, and there we go. We’ve built our report. So now
that we’re done with this report, we can then publish this
report into service. So let’s minimize this so we
can see what we’re looking at. Maybe I do a little
bit more development, and this report looks good. Now we hit “Publish”. Sure,
let’s save the changes. Test Sunset, that’s on
our desktop. There we go. It will know that my
data source lives in SharePoint Power BI demo site. So I’ll just hit “Select”
there because I do know the publish is there,
and there we go. I’m able to publish this report
directly into PowerBi.com. So now we can go
check out the report, and it will take us
back into the report. It’ll show us the report page. Everything will work like
you were expecting before. So I can still click on
these different elements and navigate my pages. So that’s working. I still have all the visual
elements that I would want. So if I want to select
certain things, I can go click these different items. It looks like my filters did not apply correctly on these values here. Then let’s reset this to default. Sure. There you go. I must have clicked
something funny there. Okay. Then the next piece
here is what happens if I want to go look at all the
reports and my datasets now. So we’re going to go
navigate back over to the workspace and we go over
here to the report section, now we have three reports. We have our Sunset, and then we have our two
other existing reports. When we go to the datasets here, you’ll notice there
is no Sunset dataset. That’s because we’re already using this existing data source. So great. We’ve made one report.
How else can we use this? What’s another way we can
continue to produce things here? So I’m going to close out
of this desktop file. There’s our first report
that we’ve built, and maybe we want a different
style or maybe we want a different way of using
some of this data. So now we can go to
this other layout. So here’s another
smooth operator layout, and we can look at how
this one’s being built. So we’ll use the same process here. We’re going to open up
this report. Here we go. Now this one has some additional
instructions with it. So with these layout, they come with instructions. Here’s the color palette
for this report. For this one, I believe
we can actually adjust. Where do I change
the color theme now? The new ribbon is throwing me off. Here it is. It’s in
the “View” ribbon now. So we’ll go here to
the “View” ribbon, and if I adjust this one, I can now pick different
color palettes. So by clicking different
color palettes, we can then automatically
see the color changes of this palette and how
that’ll look with the report. So I’m going to go with this one. We’ll maybe use this one for now, and then we can go to other pages. Something changed there
in the formatting. See how blank out my icons here? So something must have changed in the formatting for
those different icons. Let me just not save this one. We’ll try and come right back in. Well, this loads up. There we go. Okay. So now
we’re back to our report. So you can see over here we have some different shaded icons on
the upper right-hand corner. This one is a “Remove
filters” button. So while this is just
an icon on the page with a button that’s on top of it, you can see that
there’s a button here. There’s no button text. If you want, there’s an action. So you can clear filters
or clear the fillers on a page and you can
adjust these bookmarks. Again, with these layouts, there’s already predefined
or pre-built things inside the layout to help
you make things easier. Another element on this page
that you can see here is I have this hover over for
an “Information” button. So if I Ctrl + click the
“Information” button, you can see that we’ve already tried to pre-design a way for
you to put a screen over the page and then add textboxes
and show you how to do that so you could actually add additional information
about your report. So with that comes a
little “X” button. Then for each of the
page of the report, you can see a different layout of objects and they all
get this information and “Cancel” button for
each of these elements on the page. So this will just help you. Again, we’re building some predefined visualization
features on the report. So that way, as you used the report, you don’t have to build
these for every single page. You can just jump in
on the original page and start building things here. So first thing we’re going to do is, we’re going to connect
to a dataset because we have nothing in our fields currently. Let’s go to “Get data”. I’m going to go down to our datasets, and we’re going to go back
over to data source again. So I’ll just type in data source. There’s our data source. Click “Create”, and there we go. We now have our measures here. So if I open up sales again, now you’ll notice the count
that we had made earlier, I think it’s down here, is no longer there because that
was in my other file. So that’s a local measure,
not a global measure. But because we have
this global measures, we now have this here available for every single item within the dataset. So now we can go through this
one and we can start building out visuals similarly
to what we did before. So let’s build out some
information by manufacturer. I can then align my visual on the page and this
was somewhat small. So I think we could probably get away with the bar chart on this one, and then maybe we want to bring
a measure in here total units. So we’ll add values
here, and there we go. Visual is built. Maybe we
want to do another one. Let’s go grab another
visual on the page, drop in another visual here, and we will arrange him on this tile. Now we can go over here
and say let’s go for, I think I had total sales
amount. Let’s go for that one. That will be our values, and this time we may want
to bring in ProductName. This probably going
to get a bit busy. Maybe that’s what we’re
looking for there. Then we had this middle
section here for maybe some cards or some
other additional information. So here we may want to bring
in a total units measure, total units, and we may want
turned this into a card, and then we can resize this
so it fits on this bar. Then I can Ctrl + C, Ctrl + V that, and then
drop in another card there. So this one may be
total sales amount now. So we’ll swap that out. Ctrl + C and Ctrl + V, and now I can drop in another card, and let’s see what else we have here. Sales amount average per
country, maybe that’ll work. Okay. It looks like
it’ll work for me. So now that we have made
some measures here, as we click our different elements, all of our numbers should adjust. Again, we’re pulling off with
that analysis services cube. We can also adjust our
interactions here. So worldwide importers. Seems like it’s not
filtering the data. So we can go here to
the “Format” ribbon. We can edit our interactions, and we can change this from a slice mode or highlight
mode into a filter mode. So that way when I click it, it automatically filters the
data down to just that region or that additional firm. There we go. Within a couple of minutes
here, we’ve been able to produce a page that has
some information on it. We can then click this
“Information” button, and you’ll see that there’s
some weirdness here. So we’re going to double-check this. So the idea here is, as you build new elements, they’re going to be built
on top of each other. So what we want to do, is we’re going to need
to adjust the layers of these elements so
that they make sense. So let’s get rid of this thing, and now what we’ll do is we’ll
go open up the “View” ribbon. We’re going to click on
the “Selection” window, and you’ll see right away
why this is happening. So our sales amount is here
tire in this little list here. So all the things that we just recently made were at
the top of this list. Our shapes and buttons and other
images were there at the bottom. So what we need to do is, we need
to just reorient these things. So I’m actually going to
move these things down below towards the bottom of the page. We’ll put the cards
here in the middle. This is also a good idea
for using grouping. So to use grouping, you would grab the three items. So first card through the third card, right-click on those,
and then group them. So I can move my group down
and I can just rename this. Again, renaming is
definitely the way to go. So this is a list of cards, and then I have my two
charts down below. So now when I click the
“Information” button, I’ll hold Ctrl and click, now I’m able to then
wash out the page and then put little marks
over here on the top. So now I can adjust these. So the purpose of
these is, let’s see. Let’s rotate this little
button around here. So let me do it. We’re going to rotate it this way, and then we may want to
adjust these other two items. So let’s move our box here and bring our textbox
over on top of it. So now we may want to write
some information about this. Let’s go back to the textbox. So here’s where we want to say, “Hey, we want to start telling
people how to use this report. So this chart is for
units by manufacturer. Click on one of the items. I can’t spell. One of the
items, see filtered results.” You can make on any other
notes here, other notes. One thing I find that’s interesting, especially when I’m
building out reports, I’d like to tell people what kind of insightful things
we’re looking for. So a note here that we might
want to put on here is, “These are the highest sales customers”,
whatever that may be. So we’re trying to add
some insights there. So let me make this so it
all fits on one textbox. Let’s make that a little bit
bigger. Okay. There we go. Then if you want to, you can actually group these two items together. So I’m going to group
this image and textbox. I’m going to grab the Ctrl and Shift. Oops, grab these two items together, and then we will group them together. Again, right-click “Group”,
make a group out of those guys, and it looks like it made
a funny thing there again. Textbox. So now I have
another group here, and we may put that
above everything else. If I want to, I can actually add
other groups of this as well. So I can grab the entire
group that we have here and we can copy and paste that, and then we can add other
groups here as well. Now if we do this, we have to be mindful that every
time we add a new element here, we would have to update our button to be able to either show
or hide this information. So what we’ll have to do is update a bookmark as we add
more elements here. So if I click the
“Cancel” button here, you’ll see that this
one still hangs around. So we’d have to opted that bookmark. So what we’ll do is we’ll
open up the “View” ribbon, the “Bookmarks” ribbon, and you can see here we’ve
already pre-labeled things. So we have page 1, which
is just the street page, we have the “Information” button, and we have the clearing
of information. So if I click these
different various buttons, if I click on number 1 here, this is where the item
should be hidden. So we actually want
to hide that group. So what we’re going to do here is
we’re going to hide that group, then we’re going to
update this button. Now when I click there,
it should show it. So now we want to unhide both of these elements and now we’re
going to update this bookmark. So we’ll update that one. Now we should be able to toggle between not showing information
and showing information. So clicking the
“Information”, there it is. Clicking the “X” makes it disappear. So we’ve been able to
update the bookmarks, so we can then add additional
elements around each of our pages. So again, keeping some of the
navigation things simple. Now we can go through and build
out the rest of the pages, other support, if we wanted to. So let’s go back to page 1 here. Maybe we’re done and we
want to publish this now. We can now go back to
the “Home” ribbon, click on the “Publish” button,
and let let me “Save” it. So this is report number 2. I’ll save it to our desktop. It’ll then ask me
where to publish it. We’re going to go back
to the same location, and we’ve been able to
publish number 2 report. It’ll take a second
for it to load here. Now that it’s loaded, we can then go open the report in PowerBi.com. Now we’ve added our second report to the PowerBi.com’s space
using the same dataset. All right. Lastly, we can
go do this one more time. So let’s look at one
more layout here. Let’s go to this last
one called little one. This is going to be
one that’s going to be pretty flexible. All right. We’ve got about 10 minutes left. I’ll just do a quick demo here. Don’t want to belabor
the points too much. Okay. So for this one,
if you click on page 1, you’ll notice there’s a
little half-moon circle here that is going to be showing you which page
you’re highlighting. So this one, we built
this a bit later on in our report layout development. At this time, we had some
more features with buttons. So if we click on page 2 here, you’ll notice that that
little marker moves along. So you can see which
markers on which page. So as I move through the pages, I can see which page I’m on. Again, this one comes with
automatic navigation. So if I click on page 2, then that takes me to page 2 and page 3 so you can navigate
the report here. Again, this is just great
design when you have common navigational elements
across the top part of the page where you can then
just jump across the report. So let’s go to page 1. We will then go “Get data”. We’re going to go get
“Power BI datasets” again. We’re going to use our data source. I’m going to look up data source. There we go. We’re going to
create a connection to that. There we go. We have our
tables again, and here we are. So we can then continue
building on top of this. So again, we can grab maybe
product descriptions here. Maybe we want to have this at one
big list on the right-hand side. We already have our little
visual elements ready to go. Again, maybe we want to put, let’s see what else we have
here, subcategory information. Maybe by sales, sale amount. Maybe in a bar chart. So we can drop that in here. The neat part about
some of these layouts is there are white
borders for this one. So you don’t have to really get
the chart dialed in exactly right. We always know that the edges
will always line up here. So maybe we want some cards
here on the right-hand side. So I’ll drop in a card, and maybe I will put a
couple of cards over here. We’ll drop in total
units, Ctrl-C, Ctrl-V. I can drop in another card. Total NSAT now, let’s use that one. Ctrl-C, Ctrl-V. Then we can maybe go grab something
here. Maybe the year over year. Let’s see what this
does. Okay. Minus 100. The changes we adjust that. Maybe that calculation doesn’t like those filter contexts that
we’re providing for it. So anyways, there we go. I’ve been able to
build another report. You can then load up
other pages or use other different layouts,
whatever makes sense. So again, we can drop in country
again if we want it here. Now I can bring in sales
amount for this one. So we can then bring this chart
up here and reuse that as well. So you would just go
through and build out the rest of your pages
and your report, and it all will look
good and style well, and then you’ll have
this nice pre-built navigation elements at the top of the page that automatically help you navigate
through your report. Finally, we can publish this report. We hit “Save” again. This will be our third report, report number 3. I’ll hit “Save” on this. It’ll ask me then where to
publish it. Hit “Select”. Once it finishes publishing, we can then check it out
again and that’s it. So it has been able to
build three reports off of a common dataset. Each one of them have a
different style and feel, and then they all live
in the same workspace. So we’ve been able to kind of leverage the power of just
building one data model. We have these three new reports, but we’re not actually increasing the size of the datasets
or the models on this particular report.
So that’s about it. That wraps it up. So let
me minimize this one, and we’ll get out of
this report thing. I’ll hop back quickly
over to our presentation. See if I can present this
again. So there you go. That’s kind of an
overview of layouts, how you would use them, why you want to use them. Again, leveraging the
enterprise a little bit here, making a common data model, and leveraging the power of these layouts to just kind
of pre-build reports. You can build your own
report by bringing in different visual
elements and building out layouts that you
think makes sense, and then just reusing
them over and over again against different datasets. That’s about it. Are
there any questions?>>No, Mike. We haven’t
got any questions.>>Sounds good. With that, I think we have four
minutes till the end here.>>Yeah.>>I’ll give you four minutes
back to the rest of your day.>>It’s awesome presentation. So it’s pretty good
and pretty detailed.>>Thanks. We’re able to
zoom it all in there.>>Yeah. Certainly. All right. So we are closing the webinar now.




Leave a Reply

Your email address will not be published. Required fields are marked *