Forum Discussion
Sum value and running total based on unique id from another table
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.
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.
2 Replies
- vanessafvg
Community Champion
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.