PowerBi

Table of Contents

• About
• Interface
• Build A Basic Dashboard With Related Table

About

PowerBi

• desktop (most common tool)
  • Local computer tool
  • query editor is free
• service (sharing reports)
  • cloud version
  • not as good as desktop

Interface

PowerBI has three views:

1. Report (default)
2. Data (see data used in the model associated with report)
3. Model (relationships of tables for the data model)

REPORT VIEW visualisations are created

Shows available fields (can be dragged into any pane to modify visualisations).

Data Structure for PowerBi

Uses a star schema uses these tables and can be linked:

• fact → events or transactions
• dimension → more information about each transaction

Reports

Visualisations

• Cards
• Slicers
• Tables
• Fields are like columna

Build A Basic Dashboard With Related Table

Import Data

Open PowerBi and get some data Select the table you want to load

If everything looks right load it

We can preview the data here

Add Charts

Lets add a bar chart to see quantity of sales Select Quantity. If you look in the Visualisations pane, you can see that the Y-axis is Quantity. Here we see the total quantity of of sales. If you hover over the chart you can see the value. Now I want to see the quantity for each year. But, if I check my columns I only have invoice date key - no grouping for date by year. Thankfully, in the data preparation stage we created a table with a dimension for date. That contains date level information (Date, Month, Year etc). This will allow us to group the quantity by Month, Year etc without needing to clean it specifically for this case. Lets load the DimDate table to get this data.

Press Get Data, select the type and press load. Select the type

Check the format and if everything is ok, press load. In the Report > Fields tab we can see what tables are loaded If you select the Data view, we can see the raw data that has been loaded in table format

FactSale[Invoice Date Key] is linked to DimDate[Date]. The DimDate[Date] table is a Date level table, where each row is a unique date. This DimDate[Date] table can tell us more about the FactSale information. This is the essence of the Fact and Dim setup. Because we know they are linked, we can tell PowerBi this which enables us to create more useful visualtions.

Build relationships

Build relationship with the data In the relationship view, drag the values that are linked. Here we click on FactSales[Invoice Date Key] and drag and drop it onto DimDate[Date]. Update basic Bar chart Because we have told PowerBi to link the date table, we can now create a visualisation from our FactSale data with additional information from our DimDate table. As DimDate has a Year column and month column, it allows us to visualise our data by these columns. Without it, we would only be able to group by individual date. This is the power of linking data. To group by Calendar Year, go to Fields and select our Calendar year So now you see this and are probably confused. That is because, Calendar Year is on the Y axis, This means that it has just summed all of the calendar years. We don’t want to see this. We want to see the Quantity of sales (y axis) each Year (x axis). Drag the Calendar Year to the x axis Nice! That looks great, we can see the quantity of sales by year. Give yourself a high-5!

---

Return to the homepage.