Forum Discussion
Data Model for Forecast System with Pre-aggregated Forecast Values
Hi Mahhin_Shahzad1 ,
Hey, really appreciate the thorough background and your clear outline of the issue. Mixed granularity in fact tables is a classic challenge (and pain!) in data modeling for Power BI or pretty much any BI tool.
1. **Direction & Handling:**
You’re definitely on the right path by considering splitting your data into separate fact tables, especially when you have a mix of transaction-level details and pre-aggregated values. Most Power BI pros would not try to force everything into a single “everything table” because it gets messy and hard to maintain, as you’ve seen. The “Galaxy Schema” (sometimes also just called a constellation schema) pattern is pretty standard for these cases.2. **Two Fact Table / Galaxy Schema:**
Yes, this is usually the recommended approach. You keep your detailed fact table (Fact_Transactions) purely for row-level data, and another fact table (Fact_FinancialSummary) for those pre-aggregated or “black box” values. Both connect to shared dimension tables. This way, your DAX and visuals can pick the right granularity as needed, and your model stays scalable and much easier to troubleshoot.3. **Using Pre-Aggregated Totals:**
A lot of organizations do use pre-aggregated numbers from external systems, especially for financial stuff where the official source-of-truth is outside Power BI. You do lose some flexibility (like drill-down, as you said), and you may not be able to recompute those numbers exactly if business logic changes. But it’s a fair tradeoff if you can’t or shouldn’t recreate the calculations in DAX. The main thing is to make sure you clearly document what each fact table contains and keep the relationships tidy, to avoid ambiguity in your visuals.4. **Extra Resources:**
If you want to go deeper, I’d suggest checking out “The Data Warehouse Toolkit” by Kimball (if you haven’t already) – the galaxy/constellation schema is explained really well there. Also, SQLBI’s articles and videos are gold for advanced Power BI modeling tips, especially around handling multiple fact tables and composite models.One last tip: sometimes using calculation groups (if you’re on SSAS Tabular or newer Power BI features) can help manage metrics that span both granular and summary data, but that’s a bit advanced.
If you need some sample model or DAX patterns for this, let me know!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation & text editing supported by AI
Hi Mahhin_Shahzad1 ,
Yes, you are absolutely on the right track. The two-fact-table approach you've designed is an excellent and standard industry practice for handling mixed-granularity financial data. This model correctly separates your data based on its grain, leading to a system that is clearer, more performant, and easier to maintain.
Your proposed architecture, with a Fact_Transactions table for detailed, additive data and a Fact_FinancialSummary table for pre-calculated or non-additive figures, is the ideal solution. It avoids the complexity and error-prone nature of a single large table. This separation prevents accidental double-counting and allows DAX measures to be simpler and faster, as they can be targeted at the correct table without needing complex conditional logic to differentiate data types.
It is very common in financial analysis to use pre-aggregated totals from a source system, especially for complex, "black-box" calculations like taxes, which are considered the single source of truth. The trade-offs, as you've noted, are a loss of certain features. The most significant limitations are the inability to drill-down from a summary total into its constituent details and the loss of dynamic slicing. For example, a pre-calculated yearly value like 'Retained Income' cannot be correctly sliced by month; the value will simply repeat, which can be misleading if not handled carefully in your reports. You also lose calculation flexibility, as any changes must be made in the source engine.
To deepen your financial modeling skills, it's highly recommended to explore resources from industry leaders. For books, start with "The Definitive Guide to DAX" and "Analyzing Data with Power BI and Power Pivot for Excel," both by Marco Russo and Alberto Ferrari. For ongoing learning and specific patterns, their website, SQLBI.com, is the most critical resource you can find. You should also explore RADACAD.com for clear, practical articles on a wide range of Power BI topics.
Best regards,