cancel
Showing results for
Did you mean:
Highlighted
Helper I

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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

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.

3 REPLIES 3
Highlighted
Super User IV

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

@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

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Community Support

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

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.

Highlighted
Helper I

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

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!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors