Forum Discussion

ddalton's avatar
ddalton
Resolver I
4 years ago
Solved

CALCULATE SUM using matching columns?

I have two tables that fail to merge and relationships don't behave in the required manner. So, instead, I would like to sum rows in Table B and copy that value into a new column in Table A against a...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    For this you need the TREATAS function, which establishes a virtual relationship between the table in the first expression and the column(s) in the second part of the expression:

    Source: https://docs.microsoft.com/en-us/dax/treatas-function 

     

    You can  use the following:

     

    Sum Value TREATAS =
CALCULATE (
    SUM ( 'Table B'[Value] ),
    TREATAS ( VALUES ( 'Table A'[a Reference] ), 'Table B'[Reference] )
)

     

     "a" prefixed columns from table A; "b" prefixed column from table B

    I've attached the sample PBIX file

     

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

For this you need the TREATAS function, which establishes a virtual relationship between the table in the first expression and the column(s) in the second part of the expression:

Source: https://docs.microsoft.com/en-us/dax/treatas-function 

 

You can  use the following:

 

Sum Value TREATAS =
CALCULATE (
    SUM ( 'Table B'[Value] ),
    TREATAS ( VALUES ( 'Table A'[a Reference] ), 'Table B'[Reference] )
)

 

 "a" prefixed columns from table A; "b" prefixed column from table B

I've attached the sample PBIX file

 

 

ddalton's avatar
ddalton
Resolver I
4 years ago

Thanks for your reply, Paul. This solution works great!

 

EDIT: Realised I could use a stacked 100% visualisation graph. 

 

Part of the reason I wanted to create custom columns (which I didn't explain previously) was so that I could then create other Measures from those that determine the % of class type from its respective total. Then, I could show a stacked column chart that would show the percent of A, B, and C of a product (totalling 100%). 

 

Is this something that could be done using the Measure already created in your example?