Take the variation between two columns from different tables

[Anonymous]

Hi everyone. Would it be possible to take a variation column as a result from the subtraction between a value column of a table and a value column from another table? This variation needs to be a column so I can cattegorize it in intervals and put it in a histogram. Furthermore,  i would like to know if before doing the variation it is possible to apply a filter on the first value column and a different filter on the second value column and next get them side by side, substract one with each other and take the variation? 

Example:

Table A:

key	month	value
1	1	20
2	1	30
3	2	40
4	1	60
5	2	80
6	2	100

Table B:

key	month	value
1	1	20
2	1	30
3	2	40
4	1	60
5	2	80
6	2	100

Table A filtered by Month 1:

key	month	value
1	1	20
2	1	30
4	1	60

Table B filtered by Month 2:

key	month	value
3	2	40
5	2	80
6	2	100

Desired:

key	value filtered from A	value filtered from B	variantion	interval
1	20	40	-20	-20 - -10
2	30	80	-50	-50 - -40
3	60	100	-40	-30 - -40

[Greg_Deckler]

I'm not exactly understanding the calculation here. Are you trying to filter the tables and then take the "first" row in the one table and subtract the "first" row in the second table?

You might be able to do this for a very simple data set but I do not think it would scale correctly. Generally, you want some sort of relationship between the tables and use RELATED or RELATEDTABLE.

[Anonymous]

I have two tables and one filter for each of them. I would like to extract the column from table A that I filtered with a slicer marking one month and the column from table B that I filtered with a slicer  marking another random month. Once I have theses two columns from two different tables, a want to put them in a calculated table in make a variation between them, the variation would be a column in this new calculatedtable. 

[Anonymous · Accepted solution]

Hi @Anonymous,

Did these filters affect the calculate table dynamically? Current power bi does not support dynamic change tables based on filter/slicer. If you mean a calculated table based on two similar data structure table with static filters, it is possible.

Calculate table=
VAR var1 =
    ADDCOLUMNS (
        T1,
        "Group", COUNTROWS (
            FILTER (
                ALLSELECTED ( T1 ),
                [key] <= EARLIER ( [key] )
                    && [month] = EARLIER ( [month] )
            )
        )
    )
VAR var2 =
    ADDCOLUMNS (
        T2,
        "Group", COUNTROWS (
            FILTER (
                ALLSELECTED ( T2 ),
                [key] <= EARLIER ( [key] )
                    && [month] = EARLIER ( [month] )
            )
        )
    )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            DISTINCT (
                UNION (
                    SELECTCOLUMNS ( var1, "Group", [Group] ),
                    SELECTCOLUMNS ( var2, "Group", [Group] )
                )
            ),
            "filterT1", SUMX ( FILTER ( var1, [month] = 1 && [Group] = EARLIER ( [Group] ) ), [value] ),
            "filterT2", SUMX ( FILTER ( var2, [month] = 2 && [Group] = EARLIER ( [Group] ) ), [value] )
        ),
        "variantion", [filterT1] - [filterT2],"interval",'formula'
    )

BTW, I'm not so clear how your interval calculated. If they are lookup from another table, you can replace 'formula' with your expressions.

Regards,

Xiaoxin Sheng