Easy method to build dynamic dashboard in excel

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:

1. Get Data More From Folder Connect Navigate to the Folder “BI Dashboard” we have a bunch of Files Edit
2. Name the Query: “SalesZipData”
3. Select the “Extension” column Right Click Transform Lowercase
4. Filter “Extension” Text Equals ”.xlsx”
5. Select “Content” column Right click Remove Other Columns
6. Add Column Tab Custom Column Name it “GetExcelObjects” then under formula: =Excel.Workbook([Content],true)
7. Remove [Content] column
8. Expand the double arrow and uncheck “use Original name as prefix”
9. Filter “Kind” equals “Sheet”
10. Filter “Name” does not contain “Sheet”
11. Select “Name” & “Data” columns (press Shift) Remove other columns
12. Expand “Data” column Uncheck “Use prefix…”
13. Rename “State”
14. Data Types: select with CTRL “State”, “ZipCode”, “Product”, “Payment Type” Right Click Change Type Text
15. Data Types: “Date” Date, “Unit” Whole Number, Right 3 columns (CTRL) Right Click Change Type Fixed decimal Number
16. Add Column Tab Custom Column Name it “Revenue” then under formula:
    =Number.Round([Amount of Sale]*(1-[Revenue Discount]),2)
17. Data Type: “Revenue” Fixed Decimal Number
18. Remove Columns: “Amount of Sale” & “Revenue Discount”
19. Home Tab Close and Apply
20. 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
21. Create Relationships between the 2 source files
22. Rename the first Query “SalesTable”.
23. 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 down
24. Modeling Tab New Measure (look at the Fields)
    Total Revenue = Sum(SalesTable[Revenue]) note the space on either side of =
    format as $ with zero decimal
25. Modeling Tab New Measure (look at the Fields)
    Total COGS = Sum(Sales Table[COGS]) note the space on either side of =
    format as $ with zero decimal
26. Modeling Tab New Measure (look at the Fields)
    Gross Profit = [Total Revenue] – [Total COGS] note the space on either side of =
    format as $ with zero decimal
27. Modeling 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:

1. MAP: lower right corner drag ZipCodes (table b) & Gross Profit (table a)
2. BAR: left side, drag Managers of State (b) & Gross Profit (a) Test interactivity
3. 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 %”
4. 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
5. SLICER: Check State(a)
6. Multi- Row Card: Select Gross Profit (a)/ Gross Profit % (a) / Total Revenue (a) / Total COGS (a)
7. 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

Before Formatting

After Formatting