Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I want to create a custom matrix visual as below screenshot, text highlight by red are two custom row, i want additionally in the visual Total assets = current assets + long term assets, Total liability and shareholder funds = total liability + total share holder funds, total liability = current liabilities + long term liabilities.
In rows we have Groups and Sub Groups.
Groups and Sub Groups from Hier account table and metrics from fact balance.
Thanks in Advance.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |