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
Icon for Microsoft Employee rankMicrosoft 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! =)