Forum Discussion

Charcho's avatar
Charcho
Helper I
1 year ago
Solved

Operations SUMX table simple related

Hello, I have a beginner's question: How can I sum the values of attributes (A, B) and display the result in a new matrix for each? I created a DAX formula with SUMX, but it's not giving me the correct result. The two tables are related (1-*) with an attribute table. 

Need Help
Tips and Tricks
  • Charcho's avatar
    Charcho
    1 year ago

    Ashish_Mathur rajendraongole1 Deku solved my issue! Here's the link with the solution.

    https://www.dropbox.com/scl/fo/9qzy7t1nti2hgt0b2bsnr/AOXdgYX-n0wpPpMvDubjmVk?rlkey=z9g1t839bvk569oyxi550pqdr&st=rmcchlm0&dl=0

    I created two dimension tables (Fruit and Attribute) and established a relationship between them. The code I needed was. What do you think? Thank you

    Difference_Table2_Table1 =
    VAR SelectedAttribute = SELECTEDVALUE(Table1[Attribute], "None")
    VAR T_table2 =
        SUMX(
            FILTER(
                Table2,
                Table2[Attribute] = SelectedAttribute || SelectedAttribute = "None"
            ),
            Table2[Value]
        )
    VAR T_table1 =
        SUMX(
            FILTER(
                Table1,
                Table1[Attribute] = SelectedAttribute || SelectedAttribute = "None"
            ),
            Table1[Value]
        )
    RETURN
        T_table2 - T_table1

6 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    I would suggest first pivoting the data so that A and B are values in a single Category column. Then you put the Category column as a column header in your matrix and use a simple measure 

     

    Sum( tbl[value])

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Charcho  - For each attribute, you can create a measure that calculates the difference between the two tables.

     

    Difference A =
    SUMX(
    VALUES(AttributeTable[Fruit]),
    SUM(Table1[A]) - SUM(Table2[A])
    )

    Difference B =
    SUMX(
    VALUES(AttributeTable[Fruit]),
    SUM(Table1[B]) - SUM(Table2[B])
    )

    Difference Total =
    SUMX(
    VALUES(AttributeTable[Fruit]),
    SUM(Table1[Total]) - SUM(Table2[Total])
    )

    replace with your table name as per your model. Hope it works. please check.

     

  • Charcho's avatar
    Charcho
    Helper I
    1 year ago

    rajendraongole1The code isn't working as expected. Could you review it? I might be missing something: 

    Difference_A =
    SUMX(
        VALUES(Attribute[Attribute]),
        SUM(Table2[Value]) - SUM(Table1[Value])
    )
  • Charcho's avatar
    Charcho
    Helper I
    1 year ago

    Ashish_Mathur rajendraongole1 Deku solved my issue! Here's the link with the solution.

    https://www.dropbox.com/scl/fo/9qzy7t1nti2hgt0b2bsnr/AOXdgYX-n0wpPpMvDubjmVk?rlkey=z9g1t839bvk569oyxi550pqdr&st=rmcchlm0&dl=0

    I created two dimension tables (Fruit and Attribute) and established a relationship between them. The code I needed was. What do you think? Thank you

    Difference_Table2_Table1 =
    VAR SelectedAttribute = SELECTEDVALUE(Table1[Attribute], "None")
    VAR T_table2 =
        SUMX(
            FILTER(
                Table2,
                Table2[Attribute] = SelectedAttribute || SelectedAttribute = "None"
            ),
            Table2[Value]
        )
    VAR T_table1 =
        SUMX(
            FILTER(
                Table1,
                Table1[Attribute] = SelectedAttribute || SelectedAttribute = "None"
            ),
            Table1[Value]
        )
    RETURN
        T_table2 - T_table1