Sum value and running total based on unique id from another table

[kcdistiller]

I have a table with unique local id's, local text, values, standardized ID's, and standardized text that looks like this:

local ID	local Text	value	std ID	std Text
123	Ibuprofen	15	AAA	IBUPROFEN
456	Advil	4	AAA	IBUPROFEN
789	Motrin	0	AAA	IBUPROFEN
111	Amoxil	3	BBB	AMOXICILLIN
222	Trimox	5	BBB	AMOXICILLIN
333	Amoxicillin	7	BBB	AMOXICILLIN
444	E.E.S.	6	CCC	ERYTHROMYCIN

I'm trying to populate two fields in a different table, 'sum of value' and 'running total' for values grouped by the 'std ID' so it looks like this:

std ID	std Text	sum of value	running total
AAA	IBUPROFEN	19	19
BBB	AMOXICILLIN	15	34
CCC	ERYTHROMYCIN	6	40

I've found answers to MANY of my questions in this forum in addition to very useful techniques that I didn't think possible, but have not found this situation addressed.

[vanessafvg · Accepted solution]

would this article  help?

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

[vanessafvg · Accepted solution]

would this article  help?

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

[kcdistiller · Accepted solution]

What a simple solution that works so well! Using 'RELATEDTABLE' worked to access the 'value' info from another table. For posterity, the sample code on the linked page is:

DimDate[OrderSales] = 
SUMX (
    RELATEDTABLE( FactInternetSales ), 
    FactInternetSales[SalesAmount]
)

where '[OrderSales]' was replaced by a new column name in my new table, 'FactInternetSales' was replaced by my old table name, and '[SalesAmount]' was the column in the old table that held the values I was interested in collating. To add a running total, I used info from this link: https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115.