Calculate business incentives

[Anonymous]

I am doing a report to calculate business incentives.

Incentives are calculated as total sales. Therefore each product has a weight in the incentives.

I have a data table with sales:

Another table include weight:

The calculation would be something similar to:

I have a measure with total amount measuer_total_amount

When I try to calculate incentives I just know to do like this:

measue = measuer_total_amount * min(weight)

this measure performs the calculation correctly but only individually (row by row)

Problem: the total row take min value from row:

Thanks in advance

[Jihwan_Kim]

Hi, @Anonymous 

I think it really depends on how your data model looks like.

I assume the relationship between the two tables is like the below.

Please check the below picture and the sample pbix file's link down below.

To Pay =
VAR salesall =
CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ) )
RETURN
SUMX ( Sales, salesall * RELATED ( 'Weight'[Weight] ) )

https://www.dropbox.com/s/r86wsh2dbis9fcj/rjimenez.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

I can't get it to work.

The model is similar to the one in your image but sales are not grouped by products (there is more than one row for each product).

Could that be the reason?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more relevant measure.

Thank you.

[Anonymous]

Hi @Jihwan_Kim 

I hace created a example

https://sixservixdos-my.sharepoint.com/:u:/g/personal/rjimenez_varma_com/EW06T-MwLjlJsvt4IfcTKLgB4vM... 

Thank you so much for your help

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for sharing your data.

Please try the below measure.

I am not sure whether I understood your data model correctly. Because it is quite difficult for me to understand why the relationship is like this. I think it is because I do not have much information about the actual business. I hope the below measure is what you are looking for.

Please let me know.

To Pay =

VAR salesall = [TOTAL_AMOUNT_BY_CUSTOMER_AND_FISCAL_YEAR]

RETURN

SUMX ( 'Hoja1 (2)', salesall * 'Hoja1 (2)'[G.WEIGHT])

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim 

Sorry but the result is not totally correct.
In the second row you are doubling the value (Group B must return 40€).

Any ideas?

Thank you

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

But group B has two products.

How do you want to separate two products? 

[amitchandak]

@Anonymous , A new column in table 1

sum(Table1[Amount]) *maxx(filter(Table2, Table1[Product] = Table2[Product]),product2[Weight])