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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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'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