Forum Discussion
Solved
Variation estimated vs. real
Hi! I need to add a column or measure with the variation between two variables: Data looks like this: Año Categoría año Concepto Subconcepto Departamento Coste 01/01/2017 Real Ban...
Hi lmatera,
Here is the .pbix file in which I tested the scenario. If you have any question, please don't hesitate to ask.
Regards,
Yuliana Gu
Thanks again, Yu :-)
It seems that it works! in the case that I have several years in the historic BBDD, may I add the field 'Año' (year) to the new tables?
NewTable1 =
SELECTCOLUMNS (
'estimated vs real',
"Category", "Difference",
"Concepto", 'estimated vs real'[Concepto],
"Departamento", 'estimated vs real'[Departamento],
"Año", 'estimated vs real'[Año],
"Año", '
New Table2 =
SUMMARIZE (
NewTable1,
NewTable1[Departamento],
NewTable1[Concepto],
NewTable1[Category],
Newtable1[Año],
"Coste", AVERAGE ( NewTable1[diff] )
)
New Table3 =
UNION (
SELECTCOLUMNS (
'estimated vs real',
"Departamento", 'estimated vs real'[Departamento],
"Concepto", 'estimated vs real'[Concepto],
"Category", 'estimated vs real'[Category],
"Coste", 'estimated vs real'[Coste],
"Año", 'estimated vs real'[Año],
),
'New Table2'
)
I don't know if I'm doing it right.
Thank you very much! =)
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi lmatera,
Yes, if you need to do the calculation based on different date, you should consider the field 'Año' (year) when calculating the total value for [Total Real] and [Total Estimado], then add this field into new table.
For example
Total Real =
CALCULATE (
SUM ( 'estimated vs real'[Coste] ),
ALLEXCEPT (
'estimated vs real',
'estimated vs real'[Year],
'estimated vs real'[Departamento],
'estimated vs real'[Concepto],
'estimated vs real'[Category]
),
'estimated vs real'[Category] = "Real"
)
Regards,
Yuliana Gu
Works fine. Thanks, Yuliana :-)