Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kcdistiller
Frequent Visitor

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 IDlocal Textvaluestd IDstd Text
123Ibuprofen15AAAIBUPROFEN
456Advil4AAAIBUPROFEN
789Motrin0AAAIBUPROFEN
111Amoxil3BBBAMOXICILLIN
222Trimox5BBBAMOXICILLIN
333Amoxicillin7BBBAMOXICILLIN
444E.E.S.6CCCERYTHROMYCIN

 

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 IDstd Textsum of valuerunning total
AAAIBUPROFEN1919
BBBAMOXICILLIN1534
CCCERYTHROMYCIN640

 

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.

2 ACCEPTED SOLUTIONS
vanessafvg
Super User
Super User

would this article  help?

 

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

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

View solution in original post

2 REPLIES 2
vanessafvg
Super User
Super User

would this article  help?

 

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.