Join Ron Davis for an in-depth discussion in this video Demonstration of the dashboard we will be building, part of Creating Excel Power Pivot and Power View Dashboards.
- This is the dashboard, the completed dashboard that we're going to build in this video course. I would suggest if at all possible that you download the sample files. We give you some instructions for that and install the database and as I go through and explain what I'm doing, you watch that and then go ahead and do it yourself. You'll certainly learn a lot more. So what we have here is a series of tabs across the bottom and we're on the sales tab and we have a number of PivotTables in here and a few PivotCharts and as you see, they have different metrics on them.
The metrics are the numbers. So we have one, two, three PivotCharts showing different aspects of the business and then we did the same thing here in graphs. So that we would allow the users to decide whether they wanted to use the charts or the graphs, etc. As we move over we have a customer KPI. Customer KPI gives me some information so that I can analyze my sales by what we've determined to be a KPI or a Key Performance Indicator within the business.
So we ranked by high, low and medium income and then we also ranked ... Boy, we have a lot of folks making high income. I like to see that. We also ranked by degrees and blank just means they didn't tell us as we drop on down and here's the same information shown in a chart. If I come over to goals this is fairly interesting. This will end up what we call a linked table in Power Pivot, that means this Excel table that you will build will be imported by this add to data model into the data model so that we can use the information from this table within data model and Power View.
Power View is a sibling language to Power Pivot and this allows us to do some pretty cool visualizations very easily and also allows your end users that are consuming your dashboards to quickly come through and click, click, click and change different information. It's very simple to learn and it's visually very appealing. So if we go back over to sales, what I want to look at, I'm gonna go into Power Pivot and I'm gonna go into the manage tab. Here we've built out the PowerPivot data model and that's really the essential part of Power Pivot.
First let's go into diagram view and it comes up, this shows the tables that we have imported from our data source. The little arrows connecting them means relationships. If I click on one this is very handy. This shows you that store key is related to store key and it comes through and shows me this different information that I have. This data model that we build is rather basic frankly and what you'll do is you'll create these data models and you want to do a really basic one and then you'll expand out and get what your users want.
You got to kind of control them once they see the power of this because they'll rapidly expand out and they'll want other things, such as, hierarchies and new calculated fields, etc. Let's go over to data view. Down here it's what's known as a calculated field today. We used to call these metrics, which is a term that I prefer. It doesn't matter if I prefer it or not, but these are based on calculations. Let me switch back over to my workbook because I can show it to you better from here.
Calculated fields, manage calculated fields, and let's just take one such as month over month, which is just the name of it. I call it edit. So here's the code. This code is what's known as DAX, which is an expression language that we use within Power Pivot. It's similar to the Excel formula language and it's kind of like in announce of services, traditional mode MDX, but it doesn't matter. This code says use an existing sales month to date, an existing calculated fields, which we're going to reuse.
So use existing sales month to date and so to track the same month last year and that gives me my month over month. I'll cancel out and if we look in here such as goals by category, edit and this down here uses the DAX function to sum in the category goals table, which happens to be the link table that we brought in the goal columns. We brought that in and now we got that category goal, let me close. Just let me close here and that allows us to add those category goals into this spreadsheet.
So this is the spreadsheet you're going to build step-by-step as we go through this course.
- Dashboard design guidelines
- Importing data
- Relating data
- Creating calculated fields and columns
- Creating KPIs
- Adding a linked table to the data model
- Creating PivotTables and PivotCharts
- Adding Power View visualizations
- Formatting with color
- Reviewing the completed dashboard