CALCULATE SUM using matching columns?

[ddalton]

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 "matching" row. Representations of Table A and Table B are below.

Table A

Product ID	Reference
A1000	D5566
A1001	D5566
A1002	D5687
A1003	D5687
A1004	D5821
A1005	D5821
...	...

Table B

IDX	Reference	Class	Value
0001	D5566	A	100
0002	D5566	A	125
0003	D5566	B	30
0004	D5566	C	85
0005	D5687	A	100
0006	D5687	A	50
0007	D5687	B	35
0008	D5687	B	50
0009	D5687	B	10
0010	D5687	C	15
0011	D5821	A	80
0012	D5821	A	150
0013	D5821	B	40
0014	D5821	C	50
0015	D5821	C	30
...	...	...	...

I would like to create custom columns in Table A that matches the Reference columns and then sums the respective values of each class type. After such a procedure, Table A would look like the below: 

Product ID	Reference	A	B	C
A1000	D5566	225	30	85
A1001	D5566	225	30	85
A1002	D5687	150	95	15
A1003	D5687	150	95	15
A1004	D5821	230	40	80
A1005	D5821	230	40	80
...	...	...	...	...

I have tried using CALCULATE(SUM()) expressions but I'm struggling the FILTER part of the CALCULATE expression that allows me to match the Reference columns. For example, CALCULATE requires an EXPRESSION and can then take several FILTER statements, (i.e., CALCULATE(EXPRESSION, FILTER1, FILTER2, ...))

I figure my column expression would look something like this:

A = CALCULATE(SUM(TableB[Value]), TableB[Class] = "A", TableA[Reference] = TableB[Reference)

I've checked the MSFT docs on CALCULATE and believe that it can only filter on a single table so what function could I use instead to achieve this? 

[PaulDBrown · Accepted solution]

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]

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?