[MUSIC PLAYING] Welcome to MSPTDA
video number 16. Hey, in this video we're going
to study Power BI Desktop, a comprehensive introduction
to Power Query DAX dashboards and publishing in the
amazing and free Microsoft tool Power BI Desktop. Now last video, video number 15,
we studied Excel Power Pivot. Now the great news about
studying Excel Power Pivot is that everything we learned about
data modeling with Power Query and with DAX will be
virtually the same over here in Power BI Desktop. Now the goals of this video
are similar to last video, except for we have one
extra step at the end. We're going to use Power
Query to import our fact table data from CSV files and
all of our dimension tables will be from Excel. We'll import this all into
our Power BI Desktop file. Then we'll build
our data model that involves importing Power Query
transformations, relationships, calculated table for our date
table, calculated columns, and then a bunch of measures. Then we create one, two, three,
four different dashboards. Then we'll publish the
single Power BI Desktop file and all four dashboards
to an online source so we can consume the
reports on any device. This is an example of
one of the dashboards we'll create with
Power BI Desktop where we can generate embed
code to put it into a website. I can click and it
filters the report. I can move ahead to one
of the other dashboards. We can also publish it so we
can consume it on any device. If you like what you
see in this video, click that Subscribe
button and the Bell icon to get notified about new video. Now in order to
complete this project, you'll have to
download the zip folder with all of our data files. There's also PDF notes. These PDF notes cover
every single thing we do in this video. Now just to remind
you from last video, do we choose Power Pivot
or Power BI Desktop? Well, we covered this last
video but, as we'll see here, there's more visualizations
and different types of reports. The visualizations and
dashboards are interactive. And we're able to publish
over here in Power BI Desktop. Proximate history of
Power BI Desktop-- in Excel in 2009, that was
the first Power Pivot add-in. That's where we had our
first glance of the columnar database, DAX formulas,
relationships, and the data model. Power Query was debuted in 2013. Then, all of these
Power Tools were refined in Excel between 2009, 2015. And then in 2015, Microsoft
combined all these tools together and gave it away for
free called Power BI Desktop. Now there's different
versions of Power BI. Here's the Microsoft
website that describes each one of
the versions, Power BI Desktop, Power BI Pro,
and Power BI Premium. Now we're going to use
the Power BI Desktop. This is where we connect
to multiple sources clean and transform our
data into our DAX formulas and build our dashboards. But in terms of publishing
with the free version, we can publish to the
powerbi.com website. And there's some great
services once we publish it like printing, creating
PowerPoint slides, and even creating the
embed code that we can use in our own website
that's viewable to the public. But what the free version is
missing is when we publish it to powerbi.com, it's
not like the pro version where we can publish it and have
others view it on any device. Also, when we publish
it here, if you don't want to publish
it to powerbi.com but you want to
keep it on premises, then you want the
premium version. Now we are going to try the
60-day free trial because we want to see how amazing it is to
publish our powerbi.com reports and view it on any device. But if you only have the free
version, well, guess what. We can still share
two different ways. We can do it the
old fashioned way. Simply email the dot PBIX file. And then the person can simply
download the free version and view it. And of course you can use
the Embed Code option. Now downloading
Power BI Desktop. Guess what. There's two different
ways we can download it-- Microsoft download page and
Microsoft Windows Store. Now I actually just saw
a great video from Avi. There's a link in
the upper corner here where he shows you step BI
step-by-step how to download. You should go check
out that video. But here's the deal. I've been downloading
for the last few years from Microsoft download page. And that means every
month when they update it, I have to go and re-download
and reinstall it. If, however, you use the
Microsoft Windows Store link to download, then you get
automatic updates each month. On page six in our
PDF notes, there's a list of some of
the visualizations available and Power BI
Desktop with a description. We will use things like matrix,
line chart, and column and bar chart. As we use those
particular visualizations, we'll talk about the
importance of each one. Our overwriting steps
for this project. Step one, we have to
import, clean, and transform using Power Query. We'll create a date table
with DAX formulas, both table and calculated
column DAX formulas, create our relationships,
our DAX measures. We'll hide all the elements that
shouldn't be in report view, create our four dashboards,
publish our file, and refresh our data. I already downloaded
my free version and opened a blank file. I opened it from the Start menu. This is what I see when I open. Up in the title
bar I see untitled. One thing about Power BI Desktop
is that it is updated monthly. And the What's New
link is quite good. Each month it has
a good description of what's new and different. Now I'm going to close this. We need to save it. So I'm going to use F12. I named it 016 MSPTDA
intro to Power BI finished. Notice the file extension
is dot PBIX, click Save. Now this is Power BI Desktop. Up in the title we see
the name of our file. There's home ribbon,
view, modeling, and help. In the Home Ribbon tab, external
data is our Power Query. Modeling tab-- this is where we
create our DAX formulas, table DAX formulas, calculated
columns, and measures. Over here on the far
left are our icons. This is report view. data view, which really
should be called table view. We don't have any tables. Relationships view- and I'm
in the November 2018 update. And so there's a new
beta relationship view which we'll look at. Back over in report view
over here on the right-- this is a new preview
of a filter option. I'm going to close this. We'll look at that later. Visualizations-- these are
the different chart and table visualizations we can
use in our dashboard. Down here, this is where
I'll drop our fields just like in a Pivot table. Over here is where
we'll do our formatting for our visualizations. Over here is the field pane. Here is our blank white canvas. This is where we'll
create our dashboards. Down on the bottom we see tabs. Will eventually have four tabs,
four different dashboards. All of the dashboards together
will be called the report. And at the end, we can
publish this report and view all the
different dashboards. Now we want to go look at
our files we need to import. Up here we can see
016 text files. That's a zip folder you
need to download and unzip. Inside we see these files. I'm going to double click Start. It's the Start folder that we're
going to point Power Query to. So here's one, two,
three, four CSV files with our fact table data. If we look at one
of these tables, each one of these CSV files
will have ISO date, product ID, sales rep, units sold, discount,
and cost of goods sold total . These are the same files
we used last video. Each Excel workbook
has exactly one sheet. Over here in Power BI
Desktop external data, that's Power Query. We want to go to get data. I do not see from folder. So I come down to more. Get data dialog box--
there it is, folder. Click Connect,
folder path, browse. I see my Start folder. I select it. Click OK. Click OK. In this intermediate dialog
box, we want to click edit. I do not want to click
combine or load, edit. This is the Power Query editor
inside of Power BI Desktop. Almost everything in here is
the same as over in Excel. First thing we do is name our
query F transactions and enter. Here's our one single step. Over here, there's
the name of the query. Now earlier in the class,
we used the extension column to do some filtering to make
sure that we only get dot CSV and that the case was correct. Our folder will always have
the dot CSV, lowercase. So we don't need any of the
attributes for our files. Come to content, right
click, remove other columns. There it is. This is the combined
files button. When we click it, it's going to
ask us what the delimiter is. In combined files,
we want to make sure that our delimiter
is comma, click OK. Now when we click OK, it's
going to do a bunch of steps. And look at that. It created, as we've
seen many times before in this class, a sample file. Then it built a custom function,
which it used over here. There's invoke custom
function, remove other columns, expand table. That's where it
combined all the files. And finally, it
changed the type. We could see our
columns over here. There's our F transaction
where all of these steps were created. I'm going to collapse this
over in the queries pane. We want to click on our
F transaction table. We have all of these columns. ISO date-- we're actually
going to have to transform this into a proper date. Product ID and sales rep-- both of those are
foreign keys that we'll use to connect to
our dimension tables. Unit sold, discount,
total cost of goods sold-- those are all the numbers we're
going to use in our measures. Now let's do two
transformations. The first one is
I need to convert ISO date to a proper date. And Power Query
in this step right here already transformed
this to a number, but we need it as text. So we're going to click
the data type icon and convert it to text. Now it's going to ask us,
do you want to replace? And we definitely
want to replace. That means that step
right there now contains text for the ISO date. If we go up to the formula bar,
we can see ISO date type text. Now we want to convert
this a second time. So I click data type icon. And now I want to
convert it to date. And I definitely want
to add a new step. It is the combination of
text and as a separate step, date data type that converts
ISO date to a proper date. Now we'll change the
name in just a second. We have one other
transformation. Now for cost of
goods sold we want to round this
column to the penny, right click, transform,
round, round. I want two decimal
places, click OK. And there is our round step. Now we want to rename this
column, so double click. And we'll call this
date and enter. There's our last step. Now close and apply. That's different than
over in Excel Power Pivot. There, it's close and load. The close here means it will
close the Power Query editor. Apply means that it will
apply all of these steps. We're going to click,
close and apply to load this to the
Power BI Data Model. Now if we ever need
to edit this query-- and later we will have to--
we go back to edit queries and that opens up the
Power Query window. Now we want to go look at our
table in data or table view. There's our table. We could see down
here 1.1 million rows. Over here there's the
name of the table. And here's our fields. Now notice something
about these fields. What is that sigma doing? And we're in data or table view. If we go over to
report view, we see the table name and the field
names and the same icons are here. Now there's two things
we want to notice about these columns in the
fact table and that sigma icon. The first thing
is, the sigma icon over here in Power BI
Desktop means that there's a number in this column. Now I think they should
have picked a different icon because it seems like it
suggests that you can simply drag and drop. Well, in fact, you can drag and
drop this into a visualization, but it will create
an implicit measure. And as we talked
about last video, we don't want to do that. Now all the reasons we
talked about last video still apply here,
except for there's one other reason we don't
want to take a number field and create an implicit measure. And that has to do with if
you have the Power BI Pro version you can actually
download from powerbi.com the data model into Excel. And if you had
implicit measures, those measures would not show
up and there's other problems also, so no implicit measures. Now we'll create our measures
and hide the number fields later in our fact table. But the second problem
we have in the fact table are date columns. Now when we studied
Excel last video when we drag a date
column from the fact table into a pivot table,
what happened? A bunch of calculated
columns were attached to the fact table. And we don't want to do that. Well, guess what. Over in Power BI
Desktop it's even worse. If I drag this date
column from a fact table into a visualization, it will
create a complete hidden date table. Then, if I drag the date
field again and again, every time I do it, it creates
a new hidden date table. We do not want to do that. We're going to create a single
date table using DAX formulas. And then we'll
come and hide this. All right. So over in table view we
have our fact table imported. Now before we create
our date table, we need to import the
Excel dimension tables. Let's go take a look at
those Excel dimension tables. Here's our folder that we
downloaded and unzipped. That's the file. I'm going to double
click and open this. Now last video, some of our
dimension tables were in Excel and some of the were in Access. But in this video, they all
come to us inside of Excel. Now this one is fine. We'll import it as a dimension
table and build a relationship. But just as we did last
video, here's product and there's a
snowflake relationship. That means we have
category ID connected to this to get
category, supplier ID to connect to
get our supplier. Now in our reports we
need category and supplier as conditions of
criteria or filters. So rather than importing
this and building a snowflake
relationship, we're going to use Power Query to merge
these tables to bring category and supplier in. All right, I'm going
to close this file. Over here in Power BI
Desktop, we go to get data. And there it is. The first option I want to
look into an Excel file, click. There it is, double click. Just as we saw over
in Excel Power Pivot, the navigator allows us to
look into particular files and see all of the objects. We want to check each
one of our tables. Now each individual
table will be imported as a separate query. Now when we are connecting
to an SQL database it was easier because we
actually pulled the product table in and it
automatically-- because there was a relationship--
pulled the related columns. But we'll have to make
this transformation inside of Power Query. Now I click Edit, not
load or cancel, edit. Here in the query
pane, we can see we have each individual query. We want to make sure and click
through each one of these, make sure we have
the right data types. The names will be fine. Here are the steps. So you go through each one
making sure that the data types are correct. And they should all be correct. Now if we look at
D product, here is our foreign key
for category ID. So instead of doing VLOOKUP in
Excel or using relationships, we'll merge using this
as the foreign key and then over in D category
there'll be a primary key. So with D product selected,
home over to combine, dropdown for merge
queries, merge queries. The top table D
product-- and I'm going to select category ID. From the dropdown I
select D category. I select category ID. We're going to merge these in
a left outer join, click OK. Now we have this expand button. So I'm going to click
Expand, uncheck use original. Uncheck everything because
all I want is the category. This will pull the category in. And now we have our
category column. We're going to continue
merging but this time we'll select Supplier ID. That's the foreign
key, click the dropdown and select Supplier. There's our primary key,
left outer, click OK. Now we can click Expand,
uncheck everything. And we want just
supplier, click OK. This is our way
of doing a lookup and pulling that value in
using the merge feature. Now guess what. We do not want D category to be
imported into the data model. So we come over here in
the query pane, right click and uncheck enable load. We can see it's italicized. Same thing for D supplier, right
click, uncheck enable load. Now we have our two
dimension tables. We can close and load
to the data model. Over here in data
or table view, we can see we have our
dimension tables. We can select and look. Now we can go to
relationship view. It looks like it
already interpreted and created relationships. The relationships look correct,
one to many relationship. If we go over to our new
beta view, we can resize and we can also
add descriptions, selecting each table and
writing a description. Now we'll come back
to this new view when we have to hide things
because this new beta version-- it's much easier to
hide different elements than in earlier versions
of Power BI Desktop. Our next task is over
in table or data view. We want to go to modeling and
we need to create a date table. And we're going to use
new DAX table function. Now over in Excel we
created calculated columns and measures. Over here in Power
BI Desktop, we have a third option
for our DAX formulas. So our first DAX formula
over here in Power BI Desktop will be a DAX table formula. I'm going to click that button. And just like over
in Excel, we have to create our formulas
in the formula bar. I'm going to type the
name of the date table, dDate, and guess what,
over here, all we need is an equal sign. Now if you remember
in Excel, equal signs were used for
calculating columns. And colon equal sign
was used for a measure. But over here on
Power BI Desktop, we only use an equal sign. Now I'm going to type
C-A-L, down arrow, and I'm going to hit the Tab key
to get the calendar function. Now the great thing about
the calendar function, is I put the start
date, the end date, and it will automatically
create a unique list of dates from the start to the end. Now for a proper date table,
we have to start on January 1st and end on December 31st. We have to do that, because
the time intelligence functions depend on
having every single day for every single year,
from the fact table. Now how are we going to do that? Well, the first way
we're going to do that, is we're going to look using
the MIN function through fTransactions[Date] column. Now the MIN function will
pick out the minimum date. But what if it's not January 1? No problem. We're going to use the Date
function to create the January 1 date. So we're going to
first wrap year around MIN, that way
it'll find the MIN date and always get the correct year. And then, we use the Date
function, there it is. YEAR argument, I come to
the end, comma, and month is 1, comma. Day is one. Now notice this Date function is
just like it is over in Excel. And for that matter,
so is YEAR and MIN. Close parentheses. Now we have our
start date, comma. And we have to use the MAX
function on the date column. That will find the biggest day
in the whole entire fact table. We need the year, and
now we need the date. There is the year. And luckily, comma, 12 is the
month, comma, 31 is the day. Close parentheses
on date, now we have our start and end
date, close parentheses. And now when I hit enter,
I get my unique list of all the possible days
from all the possible years from the fact table. Now notice, I typed
this out linearly. And we talked about
this last video. Lots of times people like
to format their DAX code. So over here on
Power BI Desktop, I'm going to format this. Right before the
calendar, I'm going to use Shift-Enter
to get to a new line, Tab to indent, then before date. Shift-Enter, it's indented. I come to the end right
before this date, Shift-Enter. And now I'm going to get rid
of that last parentheses, because the convention is
Shift-Enter, Shift-Tab, close parentheses so it lines
up with the function. I get annoyed because I
like to close it and see it right in the formula bar. I'm going to leave
my calendar DAX table function in a linear fashion. Now for this day table, we're
going to manually create the calculated columns. We're going to use
the new column button. Our first column
will be month number. The month function,
is just like in Excel. We need to access
the date column. But over here in
Power BI Desktop, we can't click on the column
like we could over in Excel. We actually have to type
it out in the formula bar. DD, I'm going to use table
name, and in square brackets, the field name. Close parentheses and Enter. Now the month number will be
important as a helper column to help sort the next column
we're going to create, which is going to
be the month name. And I'm going to
call it just Month. Now over in Excel, we
would use the text function to format a date as
a particular word. But there is no text
function over here. In DAX, we have to use
the format function. Now the format
function works the same as the text function
does in Excel. DD Date, there's our
date column, comma. And there it is, format
just as we would an Excel. In double quotes, M-M-M-M,
that's the full month. But we want just three
M's, N, double quote, close parentheses, and enter. So we have this
column to show you why we need the month number
to help us with this month name column. Let's go over to report. Over in the field
list, I'm going to drag the actual month out
into the white, blank canvas. I'm going to expand this. And this is a table we
can see right there. And when we go to use the
paint roller here to format, there's a bunch of
different categories. Now when you're
learning how to format, for the first time, especially
when you're coming from Excel, it'll take a while
to find things. But it looks like values if I
open that and scroll way down. There's the text size. I'm going to increase
it to 15 or 16. Drag this to expand,
and we can clearly see it's sorting alphabetically. Now the reason this
didn't happen over in Excel in PowerPivot
is because we use the automatic
date table feature. So it fixed it for us. Also, if you think
about this in comparison to Excel pivot
tables, the reason that a standard pivot
table sorts correctly is because there's a custom
list behind the scenes that instructs a pivot table to sort. Not alphabetically,
but according to the calendar months. So we go back over to table,
click in the month column, and we use this sort
by column feature. Click the dropdown. And for the month
column, I'm going to say sort by month number. Now when I go back
over to report, I could see it sorts perfectly. Back over to data or table view. We want to add a
column for year. New column. And just as we would
do over in Excel, we use the year
function, and we'll use it on the dDate
date column, and Enter. Now this company has a
fiscal start year of April 1. So we're going to
have to calculate fiscal quarter and fiscal year. But first, I want
a standard quarter, because that'll be in
essence a helper column that we'll use in
subsequent formulas. So new column. Quarter is going to be
the name of the column. And just as we did in Excel
in our pre-req classes, we're going to actually
use the round up function, and we're going to look at the
month column, or month number, divided by 3 for the
first three months. That'll give us
0.33, 0.67, and one. So what do we do? We round to the integer. So round up, divide the
month number by three, and round to the integer. That gives us the
proper standard quarter. Now what do we need
for fiscal quarter? Actually, for fiscal
quarter and year. Well, April 1 is the
start of the fiscal year. So actually April, May,
June, have to be 1. That means January, February,
March is fiscal quarter 4 from the previous year. So fiscal quarter for these
records here would be four, and the year would be 2016. So I'm going to
add a new column. So fiscal quarter, and
we'll use the IF function. We're going to check whether the
standard quarter, D-D quarter is equal to 1, comma. If that's true, then we
need to go back to 4. Otherwise, we're going
to use dDate, quarter, and subtract one. That way, quarter 1
will be 4, quarter 2 will be 1, quarter 3
will be 2, and so on. Close parentheses and Enter. Now we also need fiscal year. Right here instead of
2017, we need 2016. And then the remaining 2017's
will be correct from April 1st to December 31. New column. We'll use the same logical
test, comma, but if it's true, we take that current
year and subtract one. Otherwise, we're just going
to use the current year. Close parentheses, and
both these two columns will work when the fiscal
year starts April 1. Now we need a column
for fiscal period. New column. And in this fiscal period,
we want to show Q and then join it to fiscal quarter. And then join it, ampersand. We're going to use a single
dash and double quotes. And we're going to join it to-- well we do want fiscal
year, but when I hit Enter, I actually only want
the last two digits. So we'll use the right
function which works just like it does in Excel. We'll take the right
of that, and please give me only right 2 and Enter. Now there's going to
be a problem here. Let's go over to report view. There's fiscal period. Uh oh, it sorts all
of the Q1's together. I definitely don't want that. I want Q1 17, Q2 17, and so on. So we have to go back
over to data or table view and add a special helper
column to help sort this column, new column. Now for this helper
column, we're going to take the fiscal
year and multiply it by 10. When I hit Enter, we can see
that we have an extra zero. Now we can simply add
the fiscal quarter. And when I hit Enter,
now I have a number that I can use to
sort this column. So 20164, if we scroll down,
we can see 20171, 20172, and so on. So now I select fiscal period. And I want to sort
by fiscal period. Now if we go back to report
view, there we get our sort-- Q1 17, Q2 17, and so on. Now we have a few
things we still need to do to our date table. Now we're going to have to
mark this as a date table. But before I do that,
I want to make sure that this has the
right data type. Either date time or date. I'm going to choose date. Formatting, that's just because
I want to see it over here. So I'm going to choose that
date number formatting. We will hide some
of these columns later, because some of
them are helper columns, and we don't want
them in report view. But we have to mark
this as a date table. Now back in Excel, we marked
our date table as a date table, so we didn't have
automatic grouping. And so the time
intelligence functions understood that that
was the date table. Here in Power BI Desktop, we
need to mark it as a date table so that the automatic
date tables are not created when we accidentally
drop a date field from a fact table. And we want time intelligence
functions to understand this is a date table. So I mark this as a
date table, and I'm going to choose the date column. Click OK. Two more tasks for
our date table. I'm going to go to the beta
version for relationships. We need to create a one
to many relationships, so I click and drag date
over to fTransactions. I can see a one to
many relationship. We also don't need
our helper columns to show up over in report view. Right now, we can see
all of the columns, including columns like
sort fiscal period. So back over to beta-- and I love this about
this new beta version. Month number, hold Shift,
click on Sort Fiscal Period, and I'm totally allowed to
select multiple columns. You can see multiple columns
are selected over here also. Right click, and
different than Excel. Over in Excel, it says
hide from client tool. Here, it's hide in report view. I'm going to hide those. All right, so we've completed
our date table task. We can even look over here and
see that those columns are not showing. Now the next major task
is to create our measures. Over here in data
or table view, we want to create a
bunch of measures under our fTransactions. Now we want to remind
ourselves, just like an Excel, we don't want to create
implicit measures. Now I'm totally allowed to
drag one of these numbers. I'm going to drag
units sold over here. And look at that, it
created an implicit measure. It happened to default
to a column chart. But just as over in Excel,
we don't have as much freedom with implicit measures. Things like the name of the
measure, the actual functions that go into it, the
formatting, and also there's a different problem. If you ever publish this
to powerbi.cpom and then try to download it into
Excel, implicit measures will not show up. So we want to follow a rule
that we learned over in Excel and create explicit measures. Over here,
fTransactions, modeling. The first measure we're going to
create or the first couple ones are simple sum functions. So I'm going to
click on new measure. This one will be total units. Sum fTransactions, or
we're going to add up all of the units sold. And as we talked
about last video, we always refer to
columns or fields with the full table name. And then in square
brackets, the field name. Close parentheses. We have freedom to name it,
whatever functions or formula we want to create, Enter, and
of course we can format it. Whole number is
fine, new measure. Total cost of goods sold,
summing up fTransactions. Column, cost of
goods sold total. I'm going to add currency,
English, United States. Now notice here, we have a
completely different icon than over in Excel. It looks like a
little calculator. Remember over in
Excel, it had an f(x). I think I like the f(x) better. But there you go, that
means that's a measure. That is what we
should be dragging into reports to make our
calculations, not those sigma columns. Now we're not going to
add a calculated column to calculate line revenue. In the last video, we
saw how to do that, but we also saw how to
use the sum X function. An iterator that can
simulate a helper column, but all in a single measure. So our new measure
for total revenue. And I'm going to try and build
this using the formatting. Even though, as I said before,
I don't like it so much, Shift-Enter-Tab. We're going to use sum X.
This is an awesome function. We give it the table, just like
we have this table down here, and we build a helper column. In sum X, we simply put the
table fTransaction, Tab, comma, and then we build
our formula as if it was in a calculated column. Now the first thing
we need to do, since there is a relationship
between this fTransaction and product, is to look
up the retail price. And as we learned last video,
the function we use is related. There's no V-look up in DAX. And all related needs-- since there's a relationship
between the two tables-- is what, is the name
of the column that has the item I want to
go and get and bring back to this table. So I'm going to look
up retail price. And so that look up
function iterates over every single
row in the fact table and returns the price. Well of course, we multiply
price times unit sold, Tab. Now there is a
discount for each row. You could see examples here. And that discount is the
number of pennies discounted. So we have to, in
parentheses, say, hey there's the whole dollar
minus the actual number of pennies subtracted
as a discount. So that will give us
our complete formula for calculating the line item
revenue for each row in the F transaction table. Now there's a
special name for what happens inside of sum X
or a calculated column. It's called row context. That means the related
function looks up. And we can see the
table down here. But related for each row in this
table looks up the product ID, retrieves the price. And then for these two columns
which live in that table-- and we can see the units
and the discount here-- row context allows the formula
as it iterates, or copies down through this table,
to see each row and get the appropriate values. Now we actually have to round
this and use the round function and come to the N, comma
2, close parentheses. There, we have the full
formula that will iterate. Now I didn't do my
formatting, so I'm going to
Shift-Enter-Tab, and then after related, Shift-Enter--
it's already Tabbed. Shift-Enter, 2,
Shift-Enter, Shift-Tab to line up with round,
because I'm closing off round. Shift-Enter,
Shift-Tab, and now I need to line up with sum
X, close parentheses. And now when I hit Enter, well
I don't get to see anything, we're not over in Excel where
we can see our measure grid. We can see the total
revenue icon over there. I definitely want to come up
and add United States English. I'm curious-- in
the comments, let me know-- who likes to
type it straight out and who likes to format
their DAX formulas like this. Now by the way, if you like this
formatting, it's no problem. I just don't collapse
this and then I click on a different measure. And when I click back, I can
see my formatted DAX formula. Now I want to go over to
report view and remind you about filter context which is
how our measures will calculate when we drag them
into visualizations. Now I already
created this table, dragged product over here. Now let's come down. And because it's a table,
I'm just going to check. Total revenue, total
cost of goods sold. And I can see for each row, it's
calculating the correct total revenue and cost of goods
sold for each product. But how is it doing it? Because it's the same
formula all the way down. It's not like in Excel, where
we have cell references pointing to the condition or criteria. Well, filter context. That row header condition
or criteria or filter is the product Belen. That means when the
measure hits this row, the product table is filtered
down to just the Belen product. That Belen filter flows across
the relationship, hits the fact table, and the fact
table is filtered down to just the Belen records. Then the measure total
revenue or cost of goods sold makes the calculation on
the filtered fact table. That's why it's
called filter context. Here, fact table is
filtered down to Belen. Here, it's filtered down
to Carlota and so on. All right, filter contacts. Now we have a few
more measures we want to create over
in data or table view. But before we go
over there, I want you to make a
mental note of what the total overall revenue is-- $1.5 billion. Now we're going to come
over to data table view. And our next measure is
average daily revenue. Now that calculation has
to be done in two steps. We first have to calculate the
total revenue for each day. Once we have those numbers,
then the second step is to average those numbers. Now, in order to make a
calculation like that, we have to understand
the context transition, and we have to know how to
use the calculate function. Now we covered this last
video, but I'm actually going to show you an illustrated
in a slightly different way in this video. Now I'm going to take
this entire formula. Remember, this formula
calculates total revenue. So if I can put this formula
in the context of each day, it can calculate all of
the daily revenue numbers. So I'm actually going to
copy this and then hit Enter. And guess what, I'm going
over to the dDate table. I'm going to add a new column. There it is right there. I'm going to call
it daily revenue. And guess what? What is the granularity
of this table? It's a daily granularity. So when I come up, Shift,
Enter, Control-V, that formula should work to calculate the
daily revenue for each row. That means that
the date table will be filtered down just to 1117,
1217, and so on as the formula copies down. So transactions can
see just the records for that particular day. When I hit Enter, uh oh, we
get the grand overall total for every row? Yes, the problem is,
there is no filter context in a calculated column. That fTransactions
table for this first row still has every single day
and every single transaction. But what we wanted
to see is just the transactions for 112017. Well guess what? There's row context in
a calculated column, but no filter context. No problem. There's a function that can
change the filter context. And that function is called
the calculate function. Shift-Enter. Type the letter C. I see
calculate, I hit Tab. And by putting this formula
for calculating total revenue into the expression, we don't
even have to put a filter. Calculate will take
the row context and convert it to an
equivalent filter context. So now as we copy the
formula down, fTransactions, we'll see just the
records for each day. Now I'm going to, at the end,
Shift-Enter, Shift-Tab, close parentheses. And watch the
magic of calculate. When I hit Enter, now I get
the daily total for each day. Now the name for
what Calculate did, that is, taking row
context and converting it to filter context, is
called context transition. Where is the transition? From row context
into filter context. Now here in this formula,
in a calculated column, we explicitly use the
calculate function to get the row context
to filter our table. But we do not have to do it in
a calculated column like this. If our goal is to calculate
daily revenue amounts and then use those
amounts in a formula to calculate an average,
we can do it directly in the AVERAGEX function. Now I'm going to leave
this column here for now, go over to the
fTransaction table. We're going to go up to
modeling new measure. I'm going to call this
average daily revenue, and we're going to average
using the AVERAGEX function. Now what did we just do? We had the date table, and then
we had our calculate and sum X. And that generated every
single daily total. Well the combination
of DD date table, in the first argument of average
X. And then if we wanted to, we could do calculate and
then sum X and the rest of it. And it would work perfectly. Meaning, the combination
of the first argument table dDate, with the granularity of
day, and then our calculate sum X formula to calculate
the daily totals. Internally, average X
will do exactly what the calculated column does. And then it will take the
amounts and average them. Now guess what? We do not have to manually
create that whole formula all over again. Because guess what? We already have a measure where
we calculated total revenue. Remember, if I hit Tab, that
total revenue is the sum X formula. So now we have what we want. The sum X formula
will iterate over every single day
in the dDate table, calculate the daily totals,
and then average them. But wait a second,
I thought that we had to wrap calculate
around the sum X in order to get the row
context from the first argument of average X to flow
into our measure. Well as we learned last
video, every single measure automatically has a
hidden calculate function. That means by creating the
measure first, with just sum X and then using it in
this iterative formula, we'll get exactly what we
want, context transition. So internally in sum X,
that transaction table for every row in
the dDate table will be filtered down to
just the transactions for each particular day. Now I'm going to
close parentheses. One other thing. Remember what we
said about measures? Measures, we only
use square brackets. Columns or fields, we use
the entire table and then the field name in
square brackets. Now the smartest DAX guys I
know, Marco Russo and Alberto Ferrari, they always say
this convention of using only square brackets is
awesome, because that means every time
we see a measure, we automatically know the
context transition will happen. Alright I'm going to hit Enter
and add some number formatting. And now we can use this measure
in any particular report. And whatever the filter context
from outside that flows in, that will be used by the total
revenue in addition to whatever the day is for the date table. As an example, if we
go over to report view, down to fTransactions. I have this table selected. If I check average daily
revenue, drag this down below, extend the edge, that's
the average daily revenue for the Beaut product. That's amazing,
think about that. Internally, that fact table
has this Beaut condition to filter the fact table. Then internally, it has the
single day for each row filter in the fact table also. That's the power of DAX. Now we need to build
a few more measures. We're going to go back
over to data or table view. I'm going to select
fTransactions, make sure that's selected. Modeling a new measure. This measure is going to be
total gross profit, equal sign. And guess what? We're going to use two measures. Total revenue minus
total cost of goods sold. And that will
calculate gross profit. Wherever we put this measure,
the conditions or criteria will flow in. Whether or not it
is filter context, meaning the criteria
from one of the reports. Or we happened to put
this in to an iterator where we need row context. Or, even for that matter,
a calculated column. Now we want to take
total gross profit and compare it using
division to total revenue. That will allow us to
calculate percent gross profit. Now instead of doing straight
division, as we saw last video, the divide function is great. We have numerator, denominator. And if we leave that
final argument out, if we get a divide by 0
error, it will put a blank in. So the numerator is total
gross profit, comma, and the denominator
is total revenue. Close parentheses and Enter. Oh look at that, we want to add
a percentage number formatting. So wherever we
drag that measure, the number formatting
we want will show up. Now actually, if we scroll
down, total gross profit. I forgot to add some
number formatting. Now we have a few more
measures we'll create later. I want to go over
to relationships and hide a number of
columns that we don't want to view in report view. Now when we're
hiding over here, you can hide column, measures,
or even whole tables. Date table, we already did. But in fTransactions, we want
to hide every single column. So I clicked on
the first one, hold Shift, click on the last one. Those are just the columns. Right click, hide
in report view. Now over in report view,
we'll have just our measures. Sales rep ID, we don't
want that in report view. Product ID, hide in report view. But guess what? Category ID and supplier
ID, those aren't even supposed to be there. Those were leftover from
when we merged the table. So we're actually going to go
back into power query, home, edit queries. Click on the product query,
select category and supplier ID, right click, remove columns. It'll add an extra step here. When I click Close
and Apply, it I'll close the Power Query window
and apply these changes into the data model. So I click, and now I have gone
back and edited Power Query to refine our data model. Now we're ready to build
our first dashboard. I go over to report. I'm going to click the
Plus, double click. We're going to call this
average daily gross profit. Now actually, we don't
want this anymore. I'm going to right
click, Delete page. I'm going to click Delete. Our first dashboard will involve
a number of visualizations, all with the average daily
gross profit measure. Now the way it works over
here in Power BI Desktop, is you click somewhere
in the white, you select a visualization. Here's our field list. Then we can drag and drop to
different areas in the fields area. Then, we can use the paint
roller or the Format area to format it. So our first visualization,
I click on the white. And we're going to use a
matrix, there it is right there. This is similar
to a pivot table. I'm going to click. We have our row headers,
column headers, and then the intersecting areas
where we put our measures to make our calculations. Now we're going to
have product and then a measure and some
conditional formatting. So I'm going to come
over to product. And watch this,
here is products. I'm going to click
and drag to rows. That's kind of
like a pivot table. I see my unique list. I can come over and extend. I can move the visualization. Now we want our measure for
each one of the products. Average daily gross profit. I'm going to click
and drag to values. Instantly, I see average
daily gross profit by product. Now over in formatting, if
I click on the paint roller, there's a bunch of
different areas. And as I mentioned earlier,
when you're coming from Excel, it takes a while to get
used to all of this. In the grid area, I'm going
to come down to text size and increase it to 10. Now I would like to have some
conditional formatting using data bars. So the biggest values-- there is Quad--
and [? Yanaki-- ?] those have the tallest bar. So this is conditional
formatting. And this is a matrix,
so it's listed under conditional formatting. And I can turn on data bars. If I want to go to
advance controls, I could select
things like color. I'm going to close this. And look at that. Instantly, we can see Quad and
[? Yanaki ?] are the biggest. Now why do we choose this
particular visualization? Well there's two components-- the matrix and the
conditional formatting. Anytime you choose
a matrix or a table, you actually want to see
the individual details, the individual numbers. Anytime you choose conditional
formatting or charts, you want to get a quick
visual impression. So by selecting matrix
and conditional formatting together, we get the
best of both worlds. Now next, I want a
column chart to show the same measure, average
daily gross profit, but by sales rep in region. Now a mistake that
often happens, is we're supposed to click
in the white, then click on a new visualization. But if I accidentally have
a different visualization selected and come up and
select clustered columns, well it is polite. It changes it. Control-Z, so I'm going
to click in the white, and then click on
clustered column. Now we're going to
come down to Sales rep. And we want to see how easy
it is to create a hierarchy. Now I could drag region
and sales reps separately. But we can create a hierarchy. Oftentimes, you see date
hierarchies, year, quarter, month, day. And that allows you
to drag a single item into a visualization and
have multiple fields appear. Now over in Excel, you
have to select both items and right click,
Create hierarchy. Here, watch this. I'm going to click on sales
rep, drag it over a region, and when I see the yellow
dotted line, I drop. And that creates a hierarchy. Now I can simply drag
one item to access. And I want my measure
over in values. Now I can click on
the edge and expand. Now we have to talk about
these icons right here. Without touching those icons,
if I were to click on southeast, I see just this column. And then this visualization
over here is filtered. That total right there is
the total for southeast. If I click it again, all
the columns are colored, and now I'm back to
the overall total. Now if I choose the
second button, which is turn on drill down, and
you could see it's gray. When I click on southeast, it'll
actually show me the detail. That means that the
sales rep amounts as columns for southeast,
Click and look at that, I get one, two, three sales
rep and their columns. That total is the
total for Southeast. Now if I click
Drill Up, I'm back to showing just the regions. Now I turn this off. The next button, go to the
next level in the hierarchy. Shows me the sales rep. Drill Up, and this will show
me, in this case, the labels for region and sales rep. Now the next task
is I'd like to add some conditional formatting. Over in paint
roller or format, I don't see conditional
formatting, it's actually under data colors. And I have to click
on Advanced Controls. And I want to create a rule. Rules, and I want
the rule to be for-- from the fTransaction,
the measure average daily gross profit. When it is greater
than or equal to, that means the column
height, 20,000, and less than or equal to 50,000,
I want it red. Click OK. And there I have some
conditional formatting. Now why do we choose
this visualization? Well column charts are
awesome for comparing amounts across categories. Now we don't have any
detail, but we definitely could come over
to the paintbrush. Data labels just like over
in Excel, we click on, and there are the amount. I'm going to click off. Now we have average daily
gross profit by sales rep and by product. Now we want to see
that same measure but see how it changes over
time over our fiscal period. The perfect chart for
that is a line chart. There's our line chart. Fiscal period down to axis,
average daily gross profit, over to values. And there we have
a basic line chart. We can resize, we can move. This line chart is the
perfect visualization when we want to
see how something is changing over time. Now we have one other
part of our dashboard. We actually want
to create a card. So when I click on a
particular element, it's filtering our
other visualizations. But I also want to have
some other numbers that will be filtered also. So we're going to show total
revenue, total cost of goods sold and a couple others
in what is called a card. So I'm going to
click in the white. And there's a card, but
I want multi row card. I'm going to click
and drag this, resize it, and here's
our fact table. I want to check total revenue,
that'll make it first. Total cost of goods sold,
total gross profit, and then percent gross profit. We can see our measures and
the labels over to format. Background, I'm going
to say on, and we'll have a color really light. I don't like that
bar right there. Over to the card, show bar off. Category labels, I'm going to
increase the font size to 15, data labels size 15. Now the last task
we want to select-- I'm going to resize this. We want to make sure that when
we click a particular element, it filters the other
visualizations the way we want. And if we go up to
visual tools format, I have this matrix selected,
so I click Edit Interactions. And now what I'm
going to do is go to each one of the
other visualizations and determine what happens
when I click on an element. Here, I have the off. So if I select Beaut,
it has no effect. I do want this to be
filtered, because I want to see for that product
how the average daily profit is changing over our
fiscal periods. I definitely want this
one filtered also. Down here-- I better
resize this for a second. Click back on the matrix. Then we have an option here. We can filter, and then
when we select quad, it only shows the
quad over here. But if we select the
pie or highlight, that means we still get
to see the overall total, but we'll have a visual cue
for the totals for quad. And that's how we
want to keep that one. So for each visual
element, you select it, Click Edit interactions,
and change the interactions. Now that's all we're
going to do here. Click in the white, and we have
completed our first dashboard. I can say, hey, I just want
to see [? Chin ?] [? Fam. ?] There's each one
of the products, there's the visualization over
time for the average daily gross profit, and there's
the total revenue, total cost of goods sold,
gross profit and percent gross profit. Click in the white, and
we're back to no filters. Now this dashboard analyzes
average daily gross profit based on various
conditions or criteria. Our next dashboard is going
to emphasize fiscal period. So I'm going to click
the Plus, Double Click. Type fiscal report and Enter. Click in the white
over in visualizations, I want clustered bar. We're going to
check fiscal period, then down in our measures,
we want total gross profit. We're going to resize this. This is going to be
vertical along the left. I would like to see
the visual indication, but I also want some detail,
so we come over to Format. Data Labels I want on. I also want some conditional
formatting, data colors, advanced, color scale
is exactly what I want. So to go from the
minimum red, all the way to the maximum green. Click OK. Look at that, it even
added some white, some black, and it automatically
lists it in millions. I'd like to see Excel
do that automatically. Now watch this, I'm
going to go back over to average daily gross profit. I'm going to select
this and Control-C, because I want to use the
same visualization over here. Control-V. But on this one, I
want to add data labels, on. Now down here, we're
going to use a matrix and add a bunch of
different measures. So up to matrix,
products, and I want to see percent
gross profit first. Click, then average
daily gross profit. Total units, total revenue,
cost of goods sold, total gross profit. Now over to Format. And for the grid, I want the
font size, let's say, 10. You could add some
other formatting if you want, for example,
row headers, column headers, I want to add conditional
formatting to just one of the measures total revenue. So down to conditional
formatting. Select the dropdown,
total revenue, and I want my data bars. Now let's see,
let's resize this. And I want a slicer for region. Here's the slicer. Just region, it's huge. I'm going to resize it. Bring it out in the middle. And we want a card at the top
also, resizing everything. Click in the white,
multi row card. Total units, revenue,
cost of goods sold, and total gross profit. Look at that, I don't know
what was happening there. I'm going to click
on multi row card. And then I'll resize
it, add some formatting. Now I would like to add
what's called a bookmark. I want to be able to select
northeast and southeast-- I use my Control key there-- and save that as a view. So I go up to View
and Bookmarks Pane. I can simply click
Add, and whatever I did here will be saved. I want to rename it,
I call that east view. Now I'm going to select
using the Control key. There is a particular
view, add, rename. So now we have two views. I can select, and instantly that
view of the dashboard is shown. I'm going use the eraser
to erase everything. Now our next dashboard-- I've already started it-- it's called average
transactional revenue, last 12 months. And I added a line chart. There's a line chart. And I drag total revenue. Now total revenue is
not the measure we want, we're actually going to
create two new measures. So I'm going to go
up to new measure. Actually, escape. We're going to make
this easy on ourself. We're going to go
down to total revenue. I clicked it, I can see
it up in the formula bar. I'm going to use the
same formula, Control-C, because this formula use sum X
to iterate at the transaction line level to calculate
total revenue. But we'll use the same formula
and change sum X to average X. New measure, Control-V. I'm
going to change the name, and I'm going to backspace
and change it to average X. And then hit Enter, and we're
going to add some formatting. Now if I click down
in our line chart and come down and
uncheck total revenue and check average transactional
avenue, let's uncollapse this. There's our average
transactional revenue. But we want to
compare this line. Not to calculate the
transactional average for each month, but I want to
take the average at this time period, but look at
the last 12 months. So this will be a
rolling 12 month average. Now all we have to do
since we have this measure, is change the filter context. Right there it looks
like September 2018. That measure at
that point sees all of the days for 2018 September. That filter from the date
table flows into the measure. But once it flows in,
I need to change it so that it stretches
all the way back from September 2018 all the
way back to October 1, 2017. No problem. The perfect function for
change in the filter context is calculate. So we're going to create a
new measure, rolling 12 month average transactional revenue,
equal sign, Shift-Enter-Tab, C, to get my Calculate. Tab, Shift-Enter,
and square bracket. We get our average transactional
revenue and Tab, comma, to get to our filter argument. And now, what do we need? If we were in the position in
our chart for September 2018, we have to take those valid
dates, push them back a year, create a valid list of all
the days from the year, and deliver that to calculate. So the perfect function
for this is DATESINPERIOD. DATESINPERIOD. Now the first argument, we
have to put the date column from the date table, comma. Then we need the start date. Well, wait a second. If I'm in September 2018, I need
the last day from that period. So I'm going to use-- and they
name this function smart-- LASTDATE. And we put DD date, that's
the actual date column. That column comes
from the date table. So in the current
filter context, the date table will be
filtered down to, for example, September 2018. And LASTDATE will pick out the
last day in September, comma. And this is amazing. We simply say number of
intervals, minus 1, comma. And then what is the interval? Now over in Excel, we
have the E date function to jump back or forward a
certain number of months. But look at this. We can jump back any
increment, so I want year. Now, and when I
close parentheses on dates and period, it will
see in any filter context, one year back. And the beautiful thing is, it
provides a valid list of dates through the calculate function,
which can change the filter context to the
fTransaction table, and then average transactional
revenue is calculated. Close parentheses. Now I didn't follow my
convention, I never do. Shift-Enter,
Shift-Enter, Shift-Tab, because I want to be
lined up for calculate. Close parentheses, and Enter. Now I'm going to
close this, make sure the chart is selected,
and come down here, rolling average. Next, I'm going to click
in the white, add a slicer, and I want year. So I'm going to check year. It gives me it as a slide
bar, which maybe you like. I'm going to click this
drop down and say, please give me a list. Resize it, move it, come
over to format, items. Maybe 15, slicer header, 15. Now I can select
18, 17, 19, and 20. Or erase. Later, we'll get
more years here. Finally we want a
matrix and make sure that I'm selecting the
white, click the matrix. Year into rows,
month down to rows. Down to measures. I want average daily revenue,
average transactional revenue, and our rolling. With that selected, up to
format, the grid, text size 15, resize it, move it. And I definitely
want to see both. Now we can show 17. All right, so that's
our third dashboard. Now our fourth dashboard is not
really going to be a dashboard. I just want to show
you the question feature over here in home. There's ask a question. If I click this and type, so I
simply typed out what I want, and there you go. Now, I don't
particularly use this. Because sometimes it works,
and sometimes it doesn't. If I'm over in the white
and asked the question, average daily revenue
matrix, which is what I want. Product by sales rep. I'm thinking they're going
to interpret this as a cross tabulated, but they don't. They put both
variables in the rows. Then I have to come over
here and drag sales rep down to columns to get
my cross tabulated. So you might play
around with that. Sometimes it gets it right,
sometimes it doesn't. Control-S, and now we
have completed 1, 2, 3, and 4 different dashboards. Now it's time to publish. Now we have the free
Power BI Desktop version. But we can still publish. So I'm going to click this
publish button in the share group, in the Home ribbon tab. Now it's going to
ask you to sign in, I already have an account. If you don't, click this
link for try for free. In the next window,
it's going to ask you where you want to publish it. These are different
groups of reports. We're going to publish
it to my workspace. Select. In the next window, it
says, hey, it was a success, we've published it. We click this link right here
to open it in powerbi.com. And here it is, powerbi.com Here's our one dashboard. Down here, we can see our tabs. We can click between our tabs. Select any particular
condition or criteria. We're interacting
with our report, which is made up of these
different dashboards in our online version. Now here's the amazing thing. There's a bunch of things
we can do over here. We can actually edit here
in our online version. I'm going to click edit report,
and there are our fields and our visualizations. I'm going to go back
to Reading View. Now we are viewing this, but
we can certainly share it. Now up here under
File, we can Save As, we can print the
current dashboard, we can share it by getting embed
code for SharePoint Online. We can publish to the web,
which means you get embed code, and then you have to
put it into a website. So you would actually have
to be the author of a website to use this feature. We can export it to PowerPoint. That just means if
I click this, it will generate a
PowerPoint presentation with each one of the
dashboards on a different page. We can also-- once we send
a link to this online report to someone-- if they want to download
it as a .pbix file, they can do that here. Now this published to
the web, we saw that at the beginning of the video. There it is right here. We can actually click through
and interact with our report. This is available to the public. But again, you have to
generate the embed code and be the author of
a website to do this. Now over here, these are
different workspaces. If you want to create
different workspaces, you use that button. I have gel boomerangs
in my workspace. My workspace is the
one we are using. Down here under my
workspace, these are all of the different
reports I've published. The one we're looking at
is this one right here. I also have other reports. This is Excel magic trick 1366. In this video, we learned
how to create this dashboard with these pictures of
the different products. I can select please show me 2017
gel boomerangs, and everything updates. Now back to our report, all
the way over here on the right, we want to look at
the Share button. Now I've already signed up
for my 60 day free trial for Power BI Pro. When you click this, if
you don't have an account, in the upgrade to Power
BI Pro dialog box, click the try pro for free. And it will give you
a 60 day free trial. Again, we have to have that
pro version to share it, so people can view
it on any device. I'm going to click Share, and
here's our dialog box, share. I can grant access to different
people with different email addresses. I have an email address here,
there's my email message. I can allow recipients
to share or not, send email notification? Definitely. Now before I click Share,
there's the report link. Over here, I've already granted
access to a number of people. We can also cancel at
any time, remove access. I'm going to go back to
share, and click share. Here's my email notification. I click go to report. I'm going to click go to report. And there it is,
dashboard number one, dashboard number two. And if I want to, as the
recipient download as a .pbix file or print it, I can do that. That is pretty amazing. Now back over here
in Power BI Desktop, we need to refresh our data. I've copied the
CSV data, 21 to 23, Control-C in the start
folder, Control-V. Back over in Power BI Desktop,
I can click Refresh. And now I have my
refreshed report. I can go to my fiscal
report, select Midwest, and I can see for all
of the fiscal periods. Now with the new data,
I'm going to publish this. And now over in powerbi.com,
I have my new data. That was an epic video. We saw how to create
a Power BI Desktop report with various dashboards
and publish it to powerbi.com. Over in our desktop
version, we of course, started by using Power Query
to import, clean, and transform our data. We created a date table using
DAX table functions and DAX calculated columns. We created our relationships
between tables, lots of different measures,
and then in report view, we created our
various dashboards. So in this MSPTDA
video number 16, we saw that Power BI Desktop
can do some amazing things. All right, if you
liked that video, be sure to click that thumbs up,
leave a comment, and subscribe, because there's always lots
more videos to come from Excel Is Fun. Including our next
video number 17, we're going to talk all about
the amazing calculate DAX function. All right, we'll
see you next video. If you like what you
see in this video, click that subscribe button
and the bell icon to get notified about new videos.
