Variation estimated vs. real

[lmatera]

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	Bancarios		Administración	5719,03
01/01/2017	Estimado	Bancarios		Administración	5833,41

I need an extra column at the right with the following calculation: (5719 / 5833) -1

I have more "conceptos" and rows, this is just an example.

Many thanks in advance.
Luis

[v-yulgu-msft]

Hi @lmatera,

Please try below solutions:

Add three calculated columns in your source data table:

Total Real =
CALCULATE (
    SUM ( 'estimated vs real'[Coste] ),
    ALLEXCEPT (
        'estimated vs real',
        'estimated vs real'[Departamento],
        'estimated vs real'[Concepto],
        'estimated vs real'[Category]
    ),
    'estimated vs real'[Category] = "Real"
)

Total Estimado =
CALCULATE (
    SUM ( 'estimated vs real'[Coste] ),
    ALLEXCEPT (
        'estimated vs real',
        'estimated vs real'[Departamento],
        'estimated vs real'[Concepto],
        'estimated vs real'[Category]
    ),
    'estimated vs real'[Category] = "Estimado"
)

Diff =
'estimated vs real'[Total Real] - 'estimated vs real'[Total Estimado]

Create several calculated tables referring to below formulas:

NewTable1 =
SELECTCOLUMNS (
    'estimated vs real',
    "Category", "Difference",
    "Concepto", 'estimated vs real'[Concepto],
    "Departamento", 'estimated vs real'[Departamento],
    "diff", 'estimated vs real'[Diff]
)

New Table2 =
SUMMARIZE (
    NewTable1,
    NewTable1[Departamento],
    NewTable1[Concepto],
    NewTable1[Category],
    "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]
    ),
    'New Table2'
)

Then, drag corresponding fields from 'New Table3' into matrix visual, you can get below output:

Best regards,
Yuliana Gu

[lmatera]

Hi Yuliana, 

Thank you very muchfor your quick response. I have problem creating the calculated tables referring to the formulas 😞

Would you be so kind to send me your .pbix file so I can check where's my error? 

Thanks and have a nice weekend,

Luis

[v-yulgu-msft · Accepted solution]

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

[lmatera]

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]

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]

Works fine. Thanks, Yuliana 🙂

[Datatouille]

Hi @lmatera

Try the following measures:

- Total Coste = Sum(YourTable[Coste])

- Real Coste = Calculate ( [Total Coste] , YourTable[Categoría año] = "Real" )

- Estimado Coste = Calculate ( [Total Coste] , YourTable[Categoría año] = "Estimado" )

- Real vs Estimado = [Real Coste] - [Estimado Coste]

[lmatera]

Thanks for your quick response, Excelside.

I've tried it, but it doesn't work propertly. The Values duplicate the number of columns.

I need three columns for each "Departamento":

- Estimado 

- Real

- Variation (real vs estimado)

Thanks anyway 🙂