Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Take the variation between two columns from different tables

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 col...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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