Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Comparison matrix table

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 co...
Need Help
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    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's avatar
v-kathullac
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous ,

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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