Skip to content

Steps.md

Latest commit

 

History

History
127 lines (105 loc) · 3.59 KB

Steps.md

File metadata and controls

127 lines (105 loc) · 3.59 KB

Steps to Create the Finance Dashboard in Power BI

1. Data Preparation

Transforming Data

  • Create a Pivot Table to organize data into the following columns:
    • Type: Categories like Income, Savings, Expenses, and Target.
    • Component: Sub-categories of each type (e.g., Groceries, Health).
    • Date: Timestamp for each transaction.
    • Value: The amount associated with each entry.
  • Ensure the Date column is formatted as a Date type.
  • Load the transformed data into Power BI and apply necessary formatting.

2. Calculated Measures

Key Measures

  1. Total Value: 
    Total_Value = SUM('Dataset'[Value])
  2. Income: 
    Income = CALCULATE([Total_Value], 'Dataset'[Type] = "Income")
  3. Savings: 
    Savings = CALCULATE([Total_Value], 'Dataset'[Type] = "Savings")
  4. Expenses: 
    Expenses = CALCULATE([Total_Value], 'Dataset'[Type] = "Expense")

Savings Percentage

  • Measure to calculate the percentage of income saved: 
    Saving% = DIVIDE([Savings], [Income], 0)
  • Format as Percentage with 0 decimal points.

3. Adding Date Columns

  • In Table View, create calculated columns: 
    Year = FORMAT('Dataset'[Date], "yyyy")
Month-yr = FORMAT('Dataset'[Date], "mmm-yy")

4. Report View: Building Visualizations

4.1 Slicer for Years

  • Add a Tile Slicer for the Year column:
    • Enable "Select All".
    • Adjust layout to show 1 row and 5 columns.
    • Format the slicer (e.g., set button colors, rounded borders).

4.2 Donut Charts

  • Create two Donut Charts for:
    1. Expenses by Component
    2. Savings by Component
  • Add titles, borders, and custom colors.

4.3 Line Chart

  • Add a Line Chart to show trends for Income, Savings, and Expenses over time:
    • X-Axis: Date.
    • Y-Axis: Measures (Income, Savings, Expenses).

5. Advanced Measures

Monthly Growth

  • Measure to calculate month-over-month growth: 
    Monthly_Growth = DIVIDE([Monthly_sale] - [PY_Month_Sale], [PY_Month_Sale], 0)
  • Create additional measures for specific categories (e.g., Expenses, Savings).

6. Dynamic Interactions

Dynamic Line Chart

  • Create a new table for dynamic line selection:

    Line_Selection_Table = DATATABLE("Type", STRING, "NO", INTEGER, {
    {"Income", 1}, 
    {"Savings", 2}, 
    {"Expenses", 3}, 
    {"Target", 4}
})

  • Measure for dynamic display:

    Line_Chart_Measures = 
VAR Selected_val = SELECTEDVALUE(Line_Selection_Table[NO])
RETURN SWITCH(Selected_val, 1, [Income], 2, [Savings], 3, [Expenses], 4, [Target])

Tooltip Pages

  • Set up additional pages (e.g., T1, T2) for detailed tooltips.
  • Add interactive tooltips for Donut Charts.

7. Final Touches

  • Add a slicer for Month-yr with a "Show/Hide" toggle button.
  • Format all visuals with consistent fonts, colors, and borders.
  • Add a dynamic title for the dashboard: 
    Dynamic_Title = SELECTEDVALUE(Line_Selection_Table[Type]) & " by Date"

8. Publish and Share

  • Save the Power BI file (finance_dashboard.pbix) and publish it to the Power BI service.
  • Generate a public link to share the dashboard: Finance Dashboard on Power BI.

Appendix

  • Include explanations for each measure, why it was created, and the insights it provides.
  • Add visuals and screenshots of the dashboard (from your PDF file) to illustrate each step.