Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lmatera
Frequent Visitor

Variation estimated vs. real

Hi!

I need to add a column or measure with the variation between two variables:

Data looks like this:

AñoCategoría añoConceptoSubconceptoDepartamentoCoste
01/01/2017RealBancarios Administración5719,03
01/01/2017EstimadoBancarios Administración5833,41



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

Captura.PNG


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

Many thanks in advance.
Luis

1 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

 

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-yulgu-msft
Employee
Employee

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:
1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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! 😃

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Works fine. Thanks, Yuliana 🙂

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]

 

 

Thanks for your quick response, Excelside.

 

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

Captura.PNG

 

I need three columns for each "Departamento":

 

- Estimado 

- Real

- Variation (real vs estimado)

 

Thanks anyway 🙂

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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