Forum Discussion

Kevin_Harper's avatar
Kevin_Harper
Icon for Helper I rankHelper I
5 years ago
Solved

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 - Salesp...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Kevin_Harper
    Icon for Helper I rankHelper I
    5 years ago

    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!