To create a custom matrix visual similar to the one in your first screenshot, you can follow these steps in Power BI:
1. Define Custom Measures
You’ll need to create DAX measures to calculate `Total Assets`, `Total Liabilities and Shareholder Funds`, and `Total Liability` as per the definitions you've specified.
Here are the measures to add in the Fact - Balances table:
- Total Assets:
DAX
Total Assets =
[Current Assets] + [Long Term Assets]
- Total Liability and Shareholder Funds:
DAX
Total Liability and Shareholder Funds =
[Total Liability] + [Total Shareholders Equity]
- Total Liability:
DAX
Total Liability =
[Current Liabilities] + [Long Term Liabilities]
Ensure that `[Current Assets]`, `[Long Term Assets]`, `[Current Liabilities]`, `[Long Term Liabilities]`, and `[Total Shareholders Equity]` are measures or calculated fields that return the correct sums for these categories.
2. Set Up the Matrix Visual
In Power BI:
- **Rows**: Use the `Groups` and `Sub Groups` from the **Hierarchy - Account** table. This will help structure your rows to show the hierarchical grouping.
- Values: Add the custom measures (like `Total Assets`, `Total Liability`, and `Total Liability and Shareholder Funds`) along with other metrics from the **Fact - Balances** table that you want to display.
- Columns: Use the `Date` table to add month columns if you want monthly breakdowns.
3. Custom Formatting
To highlight specific rows or categories:
- You may need to apply conditional formatting in the Matrix visual settings.
- Customize the background or font color for rows like `Total Assets` and `Total Liability and Shareholder Funds` to match the red highlights in your screenshot.
4. Additional Tips
- Ensure that relationships between **Fact - Balances**, **Hierarchy - Account**, **Account**, and **Date** tables are correctly set up to avoid any ambiguity in aggregations.
- Use Hierarchies in Power BI to organize your Groups and Sub Groups under a single field for ease in the Matrix visual.
Let me know if you'd like more guidance on setting up specific calculations or customizations!
