Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate the difference between 2 columns in 2 separate tables

Hi there.

 

The screen below shows 2 matrix (from 2 different tables).  I would like to create a 3rd matrix (in the same format as the 1st 2 matrix) whereby I can show for each financial year, the difference between the approved amount and the committed amount.

 

Any suggestio would be much appreciated.

 

Thanks.

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    Hi@irisyeong

    You can do these like me as below:

    Step 1:

    Create relationship between two table by year like this:

    Step 2:
    Add a measure: 
    Measure =
CALCULATE (
    SUMX (
        Table1,
        Table1[Qty]
            - CALCULATE (
                SUMX ( RELATEDTABLE ( Table2 ), Table2[Qty] ),
                FILTER ( Table2, Table2[ComFS] = Table1[FundingSource] )
            )
    )
)
    Step 3:
    Add third matrix
     
    Result :

    Best Regards,

    Lin

     

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi@irisyeong

    You can do these like me as below:

    Step 1:

    Create relationship between two table by year like this:

    Step 2:
    Add a measure: 
    Measure =
CALCULATE (
    SUMX (
        Table1,
        Table1[Qty]
            - CALCULATE (
                SUMX ( RELATEDTABLE ( Table2 ), Table2[Qty] ),
                FILTER ( Table2, Table2[ComFS] = Table1[FundingSource] )
            )
    )
)
    Step 3:
    Add third matrix
     
    Result :

    Best Regards,

    Lin

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Lin.  It worked.  Much appreciated.

      • toni14's avatar
        toni14
        Icon for Helper I rankHelper I
        7 years ago

        Hi,

         

        I have something similiar. I want to show difference between this two table on a month with graphics preview.

         

        Can you help me ?