How to subtract columns from two different tables in Power BI

0
votes

I am new to DAX funciton on Power BI. I have two tables as Parent table Dim_TargetSpec and Child table Fact_Yield. These tables have relationship via Spec column. I want to substract by Dim_TargetSpec[Target_Fat] - Fact_Yield[Average Fat] and this is the DAX function

Fat_Diff = Dim_TargetSpec[Target_Fat] - LOOKUPVALUE(Fact_YieldReport[Average Fat],Fact_YieldReport[Spec], Dim_TargetSpec[Spec],0)

This function returns unexpected results exactly the Dim_TargetSpec[Target_Fat] column:

    Spec    Target_Fat  Average_Fat   Fat_Diff
    AFC_2   15.2        14.2          15.2
    AFC_2   15.5        16.00         15.5

My expected correct results are:

Spec    Target_Fat  Average_Fat   Fat_Diff
AFC_2   15.2        14.2           1.0
AFC_2   15.5        16.0          -0.5

Could anyone please help me to correct the DAX function so getting correct results?

Much appreciated for your help Thanks

1
powerbidax
Why you cannot substract the "Average_Fat" value directly ? - Kin Siang
I want to add new column " Fat_Diff" on Dim_TargetSpec table, while Average_Fat is on Fact_Yield table, so that why I cann't substract the "Average_Fat" value directly - Peter Nguy Nguyen
Did you try lookup or calculate so that it will return the value on "Average_fat" column, after that you can substract value with directly? - Kin Siang

1 Answers

0
votes

Can you try this

DiffColumn=Dim_TargetSpec[Target_Fat] - RELATED(Fact_Yield[Average Fat])