Power-biConceptBeginner · 3 min read

What is Fact Table in Power BI: Simple Explanation and Example

A fact table in Power BI is a central table that stores measurable, quantitative data like sales or costs. It connects to dimension tables that describe the data, helping you analyze business metrics effectively.

⚙️

How It Works

Think of a fact table as the main ledger in a business. It records numbers like sales amounts, quantities, or profits for different transactions. Each row in the fact table represents a single event or measurement.

Dimension tables are like labels or categories that describe the facts. For example, a dimension table might list products, dates, or customers. The fact table links to these dimensions using keys, so you can slice and dice the data by product, time, or customer.

This setup helps Power BI create reports and dashboards that answer questions like "How many products sold last month?" or "What was the total revenue by region?" by combining facts with descriptive details.

💻

Example

This example shows a simple fact table with sales data linked to product and date dimensions.

DAX

FactSales = DATATABLE(
    "SaleID", INTEGER,
    "ProductID", INTEGER,
    "DateID", INTEGER,
    "Quantity", INTEGER,
    "SalesAmount", CURRENCY,
    {
        {1, 101, 20230101, 2, 50.00},
        {2, 102, 20230102, 1, 30.00},
        {3, 101, 20230102, 3, 75.00}
    }
)

ProductDimension = DATATABLE(
    "ProductID", INTEGER,
    "ProductName", STRING,
    {
        {101, "Widget"},
        {102, "Gadget"}
    }
)

DateDimension = DATATABLE(
    "DateID", INTEGER,
    "Date", DATETIME,
    {
        {20230101, DATE(2023,1,1)},
        {20230102, DATE(2023,1,2)}
    }
)

Output

FactSales table with 3 rows of sales data ProductDimension table with 2 products DateDimension table with 2 dates

🎯

When to Use

Use a fact table in Power BI when you want to analyze numeric data that changes over time or events, such as sales, expenses, or inventory counts. It is essential for building reports that summarize business performance.

For example, a retail company uses a fact table to track every sale, linking it to products, stores, and dates. This lets managers see trends, compare regions, and make data-driven decisions.

✅

Key Points

A fact table stores measurable data like sales or quantities.
It connects to dimension tables that describe the data.
Fact tables help analyze business metrics in Power BI reports.
They usually contain keys linking to dimensions and numeric values.

✅

Key Takeaways

A fact table holds the main numeric data for analysis in Power BI.

It links to dimension tables that provide context like product or date.

Use fact tables to summarize and explore business metrics effectively.

Fact tables contain keys and measurable values for detailed reporting.