Model activity and balances at their useful grains
Use shared company and period dimensions to connect operating results, budgets, and balance-sheet snapshots. A monthly actuals fact might contain company, period, account category, and amount; a budget fact also needs an approved version. A snapshot fact stores balances at a date. Relationships should support a predictable filter path rather than duplicating company attributes in every visual.
Microsoft's star-schema guidance is relevant to this separation of dimensions and facts. The specific portfolio model still depends on the sources and decisions. Do not infer a correct grain merely because Power BI can create a relationship between two columns.
Define measures before building the first page
Calculate consolidated margin by dividing summed EBITDA by summed revenue. Revenue variance is actual less budget, and variance percentage divides that difference by the relevant budget. Use a deliberate unavailable state when the denominator is zero. For working capital, use the balance at the selected period end instead of summing the balances of all displayed periods.
The demo selects one quarter at a time and shows eight quarters of company history in its downloadable data. It uses USD throughout and defines operating working capital as receivables plus inventory less payables. This is a disclosed demonstration convention, not a universal accounting definition.
| Measure | Fixture | Expected behavior |
|---|---|---|
| Portfolio EBITDA margin | A: revenue 10, EBITDA 2; B: revenue 20, EBITDA 1 | 3 / 30 = 10%, not the 12.5% average |
| Revenue variance | Actual 12; budget 11.5 | +0.5, approximately +4.35% |
| Closing working capital | Q1 balance 4; Q2 balance 5 | Q2 selection displays 5, not 9 |
| Budget percentage | Budget 0 | Unavailable; absolute variance remains visible |
Design a review surface with visible evidence
Place company and period filters near the results. Show revenue, EBITDA margin, closing working capital, and budget variance together with source coverage and the data-as-of date. A table of company contributions makes a portfolio total easier to inspect than a chart alone. Give exceptions a clear label, source reference, owner, and release implication.
Separate a working review from an approved pack. Reviewers may need to see provisional figures to investigate a problem, but those figures should not appear to be approved. A stale source or a failed tie-out must remain visible after filtering to a single company.
Test access and filter behavior in the published environment
Define who may see each company's data and who may see the consolidated portfolio. Test row-level security using the intended identities and workspace roles; access roles can affect whether RLS applies. Validate export and drillthrough behavior as well as the main dashboard. Hiding a page is not a substitute for data access control.
Refresh and access testing belong in the same release record as measure testing. Confirm that a failed source does not look like a successful current refresh, and that changes to account mapping or budgets do not silently alter historical approved outputs.
- Check one company, all companies, and every reporting period.
- Compare visual totals with an independently checked source fixture.
- Test zero denominators, missing periods, duplicate facts, and late sources.
- Verify intended user roles, exports, drillthrough, and refresh failures.
- Keep the public synthetic demo separate from confidential production models.
Primary sources
Related services and experience
- Explore the portfolio operations demo (Synthetic interactive example)
- Portfolio reporting automation (Implementation service)
- Private equity portfolio analytics (Industry overview)
Need help with this system?
Use your current reporting process to define the source, calculation, or review problem and a bounded first engagement.
Discuss portfolio reporting