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ñ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


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

Many thanks in advance.
Luis

Need Help

8 Replies

  • Datatouille's avatar
    Datatouille
    Solution Sage
    9 years ago

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

      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 :-)

       

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

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

      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