Average of the product of substract data from 2 columns in 2 different tables related by a 3rd table

[IsaacMontemayor]

Hi! I'm not an expert in PBI and I've struggled with the next problem: I have data from 2 columns, I need to substract and average the result. The problem is that these 2 columns are not related directly. They point to a third one that works as my master table that co-relates with all other tables. I need to substract FNEMes [FNENAC] - Ajustes FNE [Ajustado], and then average that result. 

I also need to create measures based on that average and data from other columns from FNEMes table. 

I sincerly appreciate your time and help. 

Charts:

Ajustes FNE

ID	Mes	Año	Ajustado
10CAN50MDD	Enero	2024	40.56
10CAN50TLM	Enero	2024	91.3863
10CAN50A2Q	Enero	2024	188.1682
10CAN50SAB	Enero	2024	0
10CAN50J9Q	Enero	2024	571.4604

Tiendas 

ID CR TIENDA	NOMBRE TIENDA
10CAN50V88	Chichi Suarez MID
10CAN502FU	IMMS Heroes MID
10CAN50CYP	La Perla MID
10CAN50VDY	Vadillo MID

FNEMes

ID	Mes	Año	Ajustado
10CAN50MDD	Enero	2024	40.56
10CAN50TLM	Enero	2024	91.3863
10CAN50A2Q	Enero	2024	188.1682
10CAN50SAB	Enero	2024	0
10CAN50J9Q	Enero	2024	571.4604

[adudani]

Hello,

Kindly provide the sample input/output in a usable format (excel, csv, table etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

Thanks,

[IsaacMontemayor]

note: added excel tables 

[v-linhuizh-msft · Accepted solution]

Hi @IsaacMontemayor ,

Thanks for your reply, @adudani .

Based on your description, l create a measure:

MEASURE =
VAR _ID =
    SELECTEDVALUE ( 'FNEMes'[ID] )
VAR _FNEM_Ajustado =
    SUMX ( FILTER ( 'FNEMes', 'FNEMes'[ID] = _ID ), 'FNEMes'[Ajustado] )
VAR _Ajsutado =
    SUMX (
        FILTER ( 'Ajustes FNE', 'Ajustes FNE'[ID] = _ID ),
        'Ajustes FNE'[Ajustado]
    )
RETURN
    AVERAGEX ( 'FNEMes', _FNEM_Ajustado - _Ajsutado )

The result is as follows, in the data you gave, the two columns are the same, so it's zero. 

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!