Multiply values in two tables

[hjg_123]

Hi,

I'm pretty new to PowerBI and struggling with how I multiply across two tables. I have a list of clients and their sales in one one table, and then I have a list of clients and the agent who services them in another table. However, Some clients have more than one agent servicing them so that sales get split between agents.

So what I would like to do is produce an output by principal agent that shows their sales. I need to match the Value column in the 'Txns' table to the agent share of each client from the 'Agents' table, multiply through and then I can sum by the Prinicpal ID. 

I have managed to link the Clients in each table together, so there is a working relationship but stuck on the next part - any help greatly appreciated - example tables are below.

I know I can add an extra column in this simple example, but in the real thing I want to be able to filter by dates etc. so need it to be an expression.

TXNs table:

Agents table

Thanks

[v-jianboli-msft · Accepted solution]

Hi @hjg_123 ,

Please try:

Measure = SUMX(Agents,CALCULATE(SUM(TXNs[Value]),FILTER('TXNs',[Clients]=EARLIER(Agents[Clients])))*[Share])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@hjg_123 

what's the expected output? could you pls also provide the screenshot and the calculation logic?