Easy method to build dynamic dashboard in excel

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

Share This Post
Have your say!
1 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.