Forum Discussion
Comparison matrix table
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.
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
9 Replies
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.
then pls provide some sample data and expected output. you can paster the data table in the reply.
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community Forum.
can you try with below mentioned points to resolve your issue
- Import both sheets into Power BI: name them Actuals (Sheet1) and Standards (Sheet2)
- Create a LinkKey column in both tables by combining [Engine Spec] and [Sub Assembly]
- In Actuals: LinkKey = Actuals[Engine Spec] & "-" & Actuals[Sub Assembly]
- In Standards: LinkKey = Standards[Engine Spec] & "-" & Standards[Sub Assembly]
- In Model view, create a relationship from Standards[LinkKey] (one side) to Actuals[LinkKey] (many side)
- Create a measure for Standard Cost: Standard Cost = MAX(Standards[Standard Cost])
- Create a measure for Actual Cost: Total Actual Cost = SUM(Actuals[Actual Cost])
- 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.
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
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
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
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
