Create dynamic monthly value and multiply value across each row of a table

[Kevin_Harper]

Hello,

I have 3 tables with columns:

Table 1 - Brokerdata

salesperson, order number, margin, delivery date, customer.

I have a measure called "Multiplier" which is ((Brokerdata.margin - Salespeople.seatcost) / Brokerdata.margin)

Table 2 - Customer (Relationship between Brokerdata.customer and Customer.customer)

customer, commission pay %, salesperson

Table 3 - Salespeople (Relationship between Customer.salesperson and Salespeople.salesperson)

salesperson, seat cost

The seat cost per salesperson is the cost to the company to employ that salesperson for the month.

The reason I created a multiplier is because each customer pays a different commission amount against the total margin per order.

For example Kraft might pay out 5% in commission per order whereas Walmart might pay out 10% in commission per order and they have the same salesperson assigned to each account.

So, to come up with the multiplier, I need to sum up all of the margin MTD, subtract the montly seat cost, then divide it all again by the MTD margin.   This allows me to accurately calculate the salesperson commission per order.

The issue I am running into is that I need to take that monthly multiplier and apply it to each daily order.  What is happening is when I add the measure "Multiplier" to the table visual, it performs that calculation using each specific orders' details to come with the multiplier rather than using the one multiplier value over the entire month.

What I need in this column is the single monthly multiplier number which would be .92 in this case.  Then I need to be able to create other calculations using this same, single value.

Hopefully this makes sense and I appreciate any suggestions.

[v-rzhou-msft · Accepted solution]

Hi @Kevin_Harper 

I think you can use All function in your Measure to make the Multiplier in the same Month equal to a single value.

Could you show me your data model, and provide me your Measure for Multiplier Column?

Or you can provide me with your pbix file by your Onedrive for Business. And this may make it easier for me to understand your require.

Best Regards,

Rico Zhou

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

[Kevin_Harper]

Hi Rico,

Here is the measure:

Seat Cost Multiplier = DIVIDE(SUM(BrokerData[Gross Margin]) - SUM(Salespeople[Seat Cost]), SUM(BrokerData[Gross Margin]))

I have a date field called "DelDate" in the "BrokerData" table.

After reading about the ALL function you suggested and looking at the examples, I tried:

Seat Cost Multiplier = CALCULATE(DIVIDE(SUM(BrokerData[Gross Margin]) - SUM(Salespeople[Seat Cost]), SUM(BrokerData[Gross Margin])), ALL(BrokerData[DelDate]))

and it worked perfectly! Exactly what I was looking for.

So simple but I don't think I would have figured that out without your suggestion.

Thank you very much!

[Greg_Deckler]

@Kevin_Harper Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.