cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

multipiers.jpg

 
 

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
Community Support

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

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. 

 

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
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

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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

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

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. 

 

 

View solution in original post

Highlighted
Helper I
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!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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