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

    • 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