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

keymonthvalue
1120
2130
3240
4160
5280
62100

 

Table B:

keymonthvalue
1120
2130
3240
4160
5280
62100

 

Table A filtered by Month 1:

keymonthvalue
1120
2130
4160

 

Table B filtered by Month 2:

keymonthvalue
3240
5280
62100

 

Desired:

keyvalue filtered from Avalue filtered from Bvariantioninterval
12040-20-20 - -10
23080-50-50 - -40
360100-40-30 - -40

 

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

     

3 Replies

  • 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