Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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
1 ACCEPTED SOLUTION
Community Support

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!

3 REPLIES 3
Super User

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,

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
New Member

note: added excel tables

Community Support

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!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors