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

Reply
IsaacMontemayor
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. 

 

IsaacMontemayor_0-1722462070979.png

 

Charts:

Ajustes FNE

IDMesAñoAjustado
10CAN50MDDEnero202440.56
10CAN50TLMEnero202491.3863
10CAN50A2QEnero2024188.1682
10CAN50SABEnero20240
10CAN50J9QEnero2024571.4604

Tiendas 

ID CR TIENDANOMBRE TIENDA
10CAN50V88Chichi Suarez MID
10CAN502FUIMMS Heroes MID
10CAN50CYPLa Perla MID
10CAN50VDYVadillo MID

FNEMes

IDMesAñoAjustado
10CAN50MDDEnero202440.56
10CAN50TLMEnero202491.3863
10CAN50A2QEnero2024188.1682
10CAN50SABEnero20240
10CAN50J9QEnero2024571.4604
1 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. 

vlinhuizhmsft_0-1722910628393.png

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!

View solution in original post

3 REPLIES 3
adudani
Super User
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

note: added excel tables 

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. 

vlinhuizhmsft_0-1722910628393.png

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 Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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