How to add Fields from Different Fact Tables to a Matrix?

[whenriques]

Hope all is well!! Is it possible to add Direct Labor from one table and Total Overrun from a different table to a matrix report?

Both tables are related to the Date table based on Period. However, when I try to create a relationship between OrgName and Organization Name, Power BI cannot create this Many-Many relationship. Any thoughts or suggestions?

Any feedback from you would be greatly appreciated!!!

[whenriques]

Thank you very much for your response. I think we are getting close, but the overrun column is missing values... Do you think it is the way I have the data in the model? The overrun data is just a fact data table that I imported from Excel? Should I restructure the model differently?

Thanks,

Willer

[v-yiruan-msft · Accepted solution]

Hi @whenriques ,

I updated your sample pbix file(see attachment). Please update the formula of measure [Total Overrun] as below, the part with red font is updated ones...

Total Overrun =
SUMX (
    FILTER (
        'Overrun Data',
        [OFRmapping] = "Overrun"
            && 'Overrun Data'[Organization] = SELECTEDVALUE ( 'TopRankingQuery'[OrgName] )
    ),
    [amount]
)

Best Regards

[whenriques]

Hi @v-yiruan-msft,

Thank you so much for your response!!! It worked great! I really appreciate you taking the time to look at my question. Your technical skills are superb!!! 🙂

Thanks again,

Willer

[jsaunders_zero9]

Start with creating a Organizations table and joining that to the Top Ranking Query and Overrun tables that will remove the many to many you currently have and allow you to do what you want to do.

[jsaunders_zero9]

If you can scrub your file of sensitive data and post a copy I can show you what I mean.

[whenriques]

Thank you! I've been trying to post the pbix file, but it is not letting me... Any ideas?

[jsaunders_zero9]

Will need to upload to a file share and post the link - OneDrive normally works well.

[whenriques]

I just sent you a link with the .pbix file. I would appreciate any feedback from you and thank you so much for taking a look. I appreciate your time and support!

[whenriques]

https://swcacorp-my.sharepoint.com/:u:/g/personal/whenriques_swca_com/ERxE1BKF0ZtKs6VSEZ_hsnsBEpkuhI... 

[jsaunders_zero9]

I have updated this version, if you have any questions let me know.

Matrix Report Question.pbix