The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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!
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,
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.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
20 | |
19 | |
18 | |
18 | |
14 |
User | Count |
---|---|
38 | |
35 | |
23 | |
20 | |
17 |