To Visual data, you can easily create a dynamic dashboard in excel. In this article, we have discussed simple method to do the same. Read to learn more
Click Here to Download Start Files
In this project:
We need to consolidate multiple Excel Files then analyze and visualize the data.
On each file we have the same table. The sheet name is a State name while we have a separate table for each manager’s data. We also have some other objects (explore the Arizona sheet)
Open Power BI Desktop
Steps for this project:
- Get Data More From Folder Connect Navigate to the Folder “BI Dashboard” we have a bunch of Files Edit
- Name the Query: “SalesZipData”
- Select the “Extension” column Right Click Transform Lowercase
- Filter “Extension” Text Equals ”.xlsx”
- Select “Content” column Right click Remove Other Columns
- Add Column Tab Custom Column Name it “GetExcelObjects” then under formula: =Excel.Workbook([Content],true)
- Remove [Content] column
- Expand the double arrow and uncheck “use Original name as prefix”
- Filter “Kind” equals “Sheet”
- Filter “Name” does not contain “Sheet”
- Select “Name” & “Data” columns (press Shift) Remove other columns
- Expand “Data” column Uncheck “Use prefix…”
- Rename “State”
- Data Types: select with CTRL “State”, “ZipCode”, “Product”, “Payment Type” Right Click Change Type Text
- Data Types: “Date” Date, “Unit” Whole Number, Right 3 columns (CTRL) Right Click Change Type Fixed decimal Number
- Add Column Tab Custom Column Name it “Revenue” then under formula:
=Number.Round([Amount of Sale]*(1-[Revenue Discount]),2) - Data Type: “Revenue” Fixed Decimal Number
- Remove Columns: “Amount of Sale” & “Revenue Discount”
- Home Tab Close and Apply
- We’ll bring another table from the start file: Get Data Excel browse and select “Managers” Table “dZipManagers” Edit Change the 2 columns to Text Close & Apply
Create Relationships between the 2 source files
- Rename the first Query “SalesTable”.
in the fact Table “SalesTable”, I want to create a calculated column that categorizes sales as “Retail” if the number of Units is less than 6 in the Sales Table click “New Column” on the Modeling Tab in the Formula bar type:
Sales Type = IF(‘Sales Table [Units]<6, “Retail”, ”Wholesale”)
hit enter to populate the function all the way downModeling Tab New Measure (look at the Fields)
Total Revenue = Sum(SalesTable[Revenue]) note the space on either side of =
format as $ with zero decimalModeling Tab New Measure (look at the Fields)
Total COGS = Sum(Sales Table[COGS]) note the space on either side of =
format as $ with zero decimalModeling Tab New Measure (look at the Fields)
Gross Profit = [Total Revenue] – [Total COGS] note the space on either side of =
format as $ with zero decimalModeling Tab New Measure (look at the Fields)
Gross Profit Percentage= [Gross Profit]/[Total Revenue] note the space on either side of =
format as $ with zero decimal
Creating Visualization on the Report View:
- MAP: lower right corner drag ZipCodes (table b) & Gross Profit (table a)
- BAR: left side, drag Managers of State (b) & Gross Profit (a) Test interactivity
- COLUMN: Right to BAR, drag Gross Profit (a)/ Gross Profit Percentage (a) / Product (a)
Change visualization to Line & Clustered Column
Drag Gross Profit Percentage to Line Values
Rename field “Gross Profit %” - Line & Clustered Column: Right to previous, drag Payment Type (a) / Gross Profit (a)/ Gross Profit Percentage (a)
Drag Gross Profit % to Line Values
Adjust and Resize - SLICER: Check State(a)
- Multi- Row Card: Select Gross Profit (a)/ Gross Profit % (a) / Total Revenue (a) / Total COGS (a)
- Test by selecting different States instantly everything is filtered
I could see Payment Method, Product, Managers, see Totals in Card
Remember we started this all out simply by getting data.
Exercise: Work on formatting your visualization!
If you want to dive deep into making dynamic dashboard, we recommend you read our friend article on creating dynamic dashboard in Excel