How to create a measure that involves another measure and lookup value.

[babukumarg]

Hi,

We need to create a measure to calculate the Commission for salesperson based on the percent confiured for each salesperson by customer and product group (Table A). we have the sales data stored by customer and product in a table (Table B) and built a measure for the total sales which involves lot of calculation based on other data we have for every sales person.

We now need to calculate the Commission for each salesperson based on the Total sales measure and the Commission percent from TABLE A. We are stuck here as it is not possible to use lookupvalue when we need to use a measure in the calculation.

How can we achieve this?

Thanks.

[v-rongtiep-msft]

Hi @babukumarg ,

I have create a simple sample. Please refer to my pbix to see if it helps you.

Create a relationship between the two tables.

Then create a measure.

Measure = [Commission]*MAX(A[percent])

If I have misunderstood your meaning, please provide your pbix without privacy information  and desired output.

Best Regards

Community Support Team _ Polly

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

[Whitewater100 · Accepted solution]

Hi:

It's difficult to answer without seeing your set up. If your discount is in one table and sales in another (hopefully related table) you can try:

SUMX(
        VALUES( 'Table A'[Discount]), 
        var _discount = 'Table A'[Discount]
        var _salesamt = [Sales Measure]
   return
      _salesamt * (1- _discount)

I hope this works!

[babukumarg]

Hi @Whitewater100,

I have given the model diagram below in my reply to @amitchandak response. Hopefully that explains my problem. I'll try your suggestion.

[amitchandak]

@babukumarg ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

One way is to bring commission % in the table having sales

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[babukumarg]

@amitchandak See below the partial model diagram that is required for this calculation. I'm not sure if I can get you the data. Total Sales Measure is based on the data in the Table highlighted Yellow. Commission percent is available in the table highlighted green. It is not possible to bring the percent into the sales table as more than one salesperson will be compensated for the a sale. 

Partial Model