0
0
Power BIbi_tool~8 mins

Star schema concept in Power BI - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Star schema concept
Business Question

How can we organize sales data to easily analyze total sales by product, region, and time?

Sample Data
Sales Fact Table
SaleIDProductIDRegionIDDateIDSalesAmount
1101201301100
2102202302200
3101201302150
4103203303300
5102202301250

Product Dimension Table
ProductIDProductNameCategory
101PenStationery
102NotebookStationery
103ChairFurniture

Region Dimension Table
RegionIDRegionName
201East
202West
203North

Date Dimension Table
DateIDDateMonthYear
3012024-01-01January2024
3022024-02-01February2024
3032024-03-01March2024
Dashboard Components
  • KPI Card: Total Sales
    Formula: Total Sales = SUM(Sales[SalesAmount])
    Result: 100 + 200 + 150 + 300 + 250 = 1000
  • Bar Chart: Sales by Product
    Formula: SUM(Sales[SalesAmount]) grouped by Product[ProductName]
    Results:
    • Pen: 100 + 150 = 250
    • Notebook: 200 + 250 = 450
    • Chair: 300
  • Pie Chart: Sales by Region
    Formula: SUM(Sales[SalesAmount]) grouped by Region[RegionName]
    Results:
    • East: 100 + 150 = 250
    • West: 200 + 250 = 450
    • North: 300
  • Table: Sales by Month
    Formula: SUM(Sales[SalesAmount]) grouped by Date[Month]
    Results:
    • January: 100 + 250 = 350
    • February: 200 + 150 = 350
    • March: 300
Dashboard Layout
+----------------+---------------------+
|   Total Sales  |   Sales by Product   |
|    (KPI)       |     (Bar Chart)      |
+----------------+---------------------+
|  Sales by Region (Pie Chart)           |
+---------------------------------------+
|          Sales by Month (Table)       |
+---------------------------------------+
Interactivity

Filters on Product Category, Region Name, and Year allow users to select specific categories, regions, or years. When a filter is applied, all components update to show sales data only for the selected items. For example, selecting Category = 'Stationery' updates the KPI, bar chart, pie chart, and table to show sales only for Pens and Notebooks.

Self Check

If you add a filter for Region = West, which components update and what sales amounts do they show?

  • All components update.
  • Total Sales: 200 + 250 = 450
  • Sales by Product: Notebook = 450
  • Sales by Region: West = 450
  • Sales by Month: January = 250, February = 200
Key Result
A dashboard showing total sales and sales breakdown by product, region, and month using a star schema data model.