Forum Discussion
Matrix design help
Hello, I have a fact table showing Projects with roles and the planned hours & revenue along with actual hours & revenue.
Sample data of fact:
This joins to Date table using the FinishDate column to be able to summarise by month in the visual.
The desired output is:
- month in the columns
- project id, role and planned hours & revenue in the rows
- actual hours & revenue as values by month and grand total
- variance between planned vs total actual
Issues I'm facing:
- how to add Planned Hours & Revenue into the rows as the data type is int and are created as explicit measures in the model?
- how to add a Variance which is: Planned Hours - SUM of all the months in the context?
Link to sample PBIX file:
Thank you.
Hi _bs_ ,
Power BI Matrix visuals have inherent limitations. Row headers can only come from table columns, not measures, so measures like Planned Hours and Planned Revenue cannot be placed directly in Rows and are evaluated only in the Values area. Additionally, the Matrix does not natively support combining static planned values with time-based actuals.
If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:
Power BI Partners | Microsoft Power Platform
You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
we truly appreciate your active participation in the Microsoft Fabric Community.Thanks,
Prashanth
15 Replies
Hi _bs_
What you're trying to achieve while possible isn't doable out of the box. This will require a disconnected table containing a column for the month names + total and variance, a column for the subheaders (actual revenue, actual hours, revenue, hours) and then a sort column and possibly a column for the format strings (currency, percentage, whole number, etc). The column values to show are controlled by a measure referencing those.
Attached are sample pbix files for your reference.
Hi danextian, I've had partial success.
By using the nested headers example I am able to produce a visual showing the actuals by year/month/role and understand how the CROSSJOIN works.
I'm struggling with how VAR _Type works.
Each Project ID can have more than 1 role, therefore Planned Effort & Revenue needs to be aggreagated by Role per Project.
Any suggestions?
Thanks
Hi _bs_ ,
Power BI Matrix visuals have inherent limitations. Row headers can only come from table columns, not measures, so measures like Planned Hours and Planned Revenue cannot be placed directly in Rows and are evaluated only in the Values area. Additionally, the Matrix does not natively support combining static planned values with time-based actuals.
If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:
Power BI Partners | Microsoft Power Platform
You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
we truly appreciate your active participation in the Microsoft Fabric Community.Thanks,
Prashanth
Hi,
Share the download link of t he MS Excel flie with your input data and expected result already shown there. Much easier to understand an MS Excel rather than an image.
Hi Ashish_Mathur, it says: The file type (.xlsx) is not supported, not sure if I'm doing something wrong as I edited the original posted and tried to drop the file.
You just cannot drop a file. You have to share the download link.
Hi _bs_,
As we haven't heard back from you, we would like you to please share the sample PBIX file shown above? This will help in understanding your scenario and providing an accurate solution.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportHi @_bs_,
As we haven't heard back from you, we would like you to please share the sample PBIX file shown above? This will help in understanding your scenario and providing an accurate solution.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportHi _bs_ ,
Please refer below, How to provide sample data in the Power BI Forum
You can refer the following link to upload the file to the community.
How to upload PBI in CommunityWe are available to support you and are committed to helping you reach a resolution.
Best Regards,
Prashanth
