Forum Discussion
Comparison matrix table
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
Super Userhave 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.
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.