cancel
Showing results for
Did you mean:
Regular Visitor

Multiply values in two tables

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

1 ACCEPTED SOLUTION
Community Support

Hi @hjg_123 ,

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.

2 REPLIES 2
Community Support

Hi @hjg_123 ,

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.

Super User

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

Proud to be a Super User!

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors