Comparison matrix table

[Chxlsea_]

Hi, I'm currently trying to create a table which compares the standard cost to the actual cost. So far I have the standard cost by category/sub assembly however, I'm having trouble with the actual costs. Currently the actual costs are pulling through as a whole sum. I have attached a picture for reference. 

Hi, I'm currently trying to create a table which compares the standard cost to the actual cost. So far I have the standard cost by category/sub assembly however, I'm having trouble with the actual costs. Currently the actual costs are pulling through as a whole sum. I have attached a picture for reference. 

My data set has two sheets. Sheet one has the columns project definition, engine spec, sub assembly and the actual costs. In this sheet each sub assembly has multiple actual costs. Sheet two has only the engine spec, sub assembly and standard cost. In sheet two each sub assembly only has one standard cost, compared to sheet one where each sub assembly has multiple. I want to compare the standard costs to the actual costs by project definition. This means the matrix visual would need to filter by which ever project definition the user chooses, link to the engine spec and bring back the relevant sub assemblies with their standard cost. it then needs to sum up the actual costs from sheet one by project definition and sub assembly. 

[v-kathullac]

Hi @Chxlsea_ ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[v-kathullac]

Hi @Chxlsea_ ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[v-kathullac]

Hi @Chxlsea_ ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[v-kathullac · Accepted solution]

Hi 

Thank you for reaching out to Microsoft Fabric Community Forum

Below are the few points that will resolve your issue let us know if you need any further assistance.

• Your current Standard Cost = MAX(Standards[Standard Cost]) repeats the same value for each matching row, which may cause duplication or slight averaging in visuals.
• This happens when multiple rows in Actuals match the same Sub Assembly in Standards due to a many-to-one relationship.
• If the Standard Cost varies by Project Definition, update the LinkKey in both tables to include Project Definition.
• Update LinkKey in Actuals as: Actuals[Engine Spec] & "-" & Actuals[Sub Assembly] & "-" & Actuals[Project Definition]
• Update LinkKey in Standards as: Standards[Engine Spec] & "-" & Standards[Sub Assembly] & "-" & Standards[Project Definition]
• Recreate the relationship: Standards[LinkKey] (one) → Actuals[LinkKey] (many)
• If Standard Cost is fixed per Sub Assembly, use LOOKUPVALUE instead of a relationship

Please follow the below dax 

Standard Cost = LOOKUPVALUE(
Standards[Standard Cost],
Standards[Sub Assembly], Actuals[Sub Assembly],
Standards[Engine Spec], Actuals[Engine Spec]
)

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya

[v-kathullac]

Hi @Chxlsea_ ,

Thank you for reaching out to Microsoft Fabric Community Forum.

can you try with below mentioned points to resolve your issue

1. Import both sheets into Power BI: name them Actuals (Sheet1) and Standards (Sheet2)
2. Create a LinkKey column in both tables by combining [Engine Spec] and [Sub Assembly]
3. In Actuals: LinkKey = Actuals[Engine Spec] & "-" & Actuals[Sub Assembly]
4. In Standards: LinkKey = Standards[Engine Spec] & "-" & Standards[Sub Assembly]
5. In Model view, create a relationship from Standards[LinkKey] (one side) to Actuals[LinkKey] (many side)
6. Create a measure for Standard Cost: Standard Cost = MAX(Standards[Standard Cost])
7. Create a measure for Actual Cost: Total Actual Cost = SUM(Actuals[Actual Cost])
8. Create a Matrix visual :

• Use Actuals[Sub Assembly] in rows
• Use Actuals[Project Definition] in filters or columns
• Use Standard Cost and Total Actual Cost as values in the matrix
• This setup will ensure Standard Cost shows correctly and Actual Cost is summed by sub assembly and project definition

Regards,

Chaithanya.

[Chxlsea_]

Hi, this works for the actual costs however for the standard each sub assembly still has the same cost but now its slightly lower 

[ryan_mayu]

have you created the relationship between two tables? 

or maybe you can try to create a dim table and connect dim table to these two tables. In the matrix, you need to use the sub assembly column from the dim table, the actual cost will show corresponding value.

if you still can't figure this out, pls provide some sample data of these two sheets.

[Chxlsea_]

I have a DIM table for project definition, engine spec, sub assembly. the relationships are also set up for these. 

[ryan_mayu]

then pls provide some sample data and expected output. you can paster the data table in the reply.