Forum Discussion

flunte70's avatar
flunte70
Helper I
8 years ago
Solved

Difference between 2 columns in a Matrix Table

I have a report with a matrix table with two columns that I want to show the difference between.

I cannot do this calculation in the Data table as it is the same column.

 

My data is in a table called "Data" and the fields are called "Type" and "Total".

'Data'[Type] contains several data series like "Actual", "Budget", etc. & 'Data'[Total] contains values.

 

I my matrix table in my report the column is 'Data'[Type] which is filtered into two columns "Actual" and "Budget" and Values is 'Data'[Total].

 

Now I want to make a third column called "Deviation" showing the difference between Actual and Budget.

 

Here is a screendump of my matrix:

 

 

Need Help
  • flunte70's avatar
    flunte70
    8 years ago

    Thanks for your fast reply!

     

    Someone advised me to change my matrix table to a normal table by filtering my dataset so now it's much easier to do the excercise.

     

    But thanks anyway!

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi flunte70,

     

    Try something like this:

    Deviation =
CALCULATE ( SUM ( Data[Total] ), Data[Type] = "Actual" )
    - CALCULATE ( SUM ( Data[Total] ), Data[Type] = "Budget" )

    Should work.

     

    Regards,

    MFelix

     

    • flunte70's avatar
      flunte70
      Helper I
      8 years ago

      Thanks for your fast reply!

       

      Someone advised me to change my matrix table to a normal table by filtering my dataset so now it's much easier to do the excercise.

       

      But thanks anyway!