Forum Discussion

lmatera's avatar
lmatera
Frequent Visitor
9 years ago
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...
Need Help
lmatera's avatar
lmatera
Frequent Visitor
9 years ago

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's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi 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

    • lmatera's avatar
      lmatera
      Frequent Visitor
      9 years ago

      Works fine. Thanks, Yuliana :-)