Matrix design help

[_bs_]

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:

1. how to add Planned Hours & Revenue into the rows as the data type is int and are created as explicit measures in the model?
2. how to add a Variance which is: Planned Hours - SUM of all the months in the context?

Link to sample PBIX file:

ProjectSample

Thank you.

[v-prasare]

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

[v-prasare]

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 support

[_bs_]

Hello @v-prasare, I have now created sample pbix file.

However there is no option to upload or add to this post?

[v-prasare]

Hi @_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 Community

We are available to support you and are committed to helping you reach a resolution.

Best Regards,

Prashanth

[_bs_]

Thank you @v-prasare.

I have created a link in the original message.

[v-prasare]

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 support

[danextian]

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. 

[_bs_]

Thank you @danextian, I will look at your examples and attempt to replicate and let you how I get on.

[_bs_]

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

[v-prasare]

Hi @_bs_,

did your query got resolved or you still needed help here. if your query got respolved, can you please share your solution here this will be helpful for other users with similar issues.

thanks,

Prashanth

[_bs_]

Hi @v-prasare  - not fully resolved as I the solution provided didn't work for me.

[v-prasare]

Could you please share the sample PBIX file shown above? This will help in understanding your scenario and providing an accurate solution.

Please also provide sample data that fully represents your issue, in a usable format (not screenshots). Ensure the data does not contain any sensitive information and is only related to the problem.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[Ashish_Mathur]

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.

[_bs_]

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.

[Ashish_Mathur]

You just cannot drop a file.  You have to share the download link.