Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subtracting 2 different columns from 2 different tables

Hi,   I am trying to calculate the difference between 2 values from 2 different tables. The data model consists of 3 tables and 5 dimension tables. The 2 tables that have the columns does not have ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    Hope the following formula could work.

    Variance (Value 1 - Value 2) =
VAR tab =
    CROSSJOIN ( 'D:Location', 'D:Date' )
VAR tab2 =
    FILTER (
        ADDCOLUMNS (
            tab,
            "v1",
                CALCULATE (
                    SUM ( 'F: Forecasted Data'[Value 1] ),
                    FILTER (
                        tab,
                        [Date] = EARLIER ( 'D:Date'[Date] )
                            && [sheetName] = EARLIER ( 'D:Location'[sheetName] )
                    )
                ),
            "v2",
                CALCULATE (
                    SUM ( 'F: Confirmed Data'[Value 2] ),
                    FILTER (
                        tab,
                        [Date] = EARLIER ( 'D:Date'[Date] )
                            && [sheetName] = EARLIER ( 'D:Location'[sheetName] )
                    )
                )
        ),
        [v1] <> BLANK ()
            && [v2] <> BLANK ()
    )
RETURN
    SUMX ( tab2, [v1] - [v2] )

    Best Regards,
    Community Support Team _ Eason

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try return like

 

 

return
sumx(summarize('F: Forecasted Data', 'D: Location' [location], Date[Date], "_1", IF(NOT ISBLANK(__MEASURE_VALUE) && NOT ISBLANK(__BASELINE_VALUE), __BASELINE_VALUE - __MEASURE_VALUE )), [_1])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak,

    Here is what I got:

     

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, Anonymous 

      Hope the following formula could work.

      Variance (Value 1 - Value 2) =
VAR tab =
    CROSSJOIN ( 'D:Location', 'D:Date' )
VAR tab2 =
    FILTER (
        ADDCOLUMNS (
            tab,
            "v1",
                CALCULATE (
                    SUM ( 'F: Forecasted Data'[Value 1] ),
                    FILTER (
                        tab,
                        [Date] = EARLIER ( 'D:Date'[Date] )
                            && [sheetName] = EARLIER ( 'D:Location'[sheetName] )
                    )
                ),
            "v2",
                CALCULATE (
                    SUM ( 'F: Confirmed Data'[Value 2] ),
                    FILTER (
                        tab,
                        [Date] = EARLIER ( 'D:Date'[Date] )
                            && [sheetName] = EARLIER ( 'D:Location'[sheetName] )
                    )
                )
        ),
        [v1] <> BLANK ()
            && [v2] <> BLANK ()
    )
RETURN
    SUMX ( tab2, [v1] - [v2] )

      Best Regards,
      Community Support Team _ Eason