Complex calculation DAX - need ideas

[Vander1981]

Hi guys,

Here is my problem :

I would like to create a measure for incentive calculation. Below my table :

I have got a table with following columns : Date; Sales Reps; Products; Actuals Revenue ; Budget Revenue ; Weight
This table have relationships with Dimension Date table and Dimension Sales Reps.

The idea is that each of the 3 products per sales reps has a different weight, let's say 30%, 20% and 50%
That weight has to be multiplied by the ratio (Actuals Revenue / Budget Revenue).
If you sum up the result for the 3 products you obtain a total % of achievement.
When the time period is only one month, this can be done with a SUMX over that table.

However, when YTD calculation has to be performed I am completely lost. Why ? Because then the SUMX does not work anymore.
Indeed, it iterates over each rows, as there are multiple months, ratio per products are summed over month...which is not good.

With a basic sum, I don't know how to allocate the weight per products.
I tried something with function table removing the date column but then I lose my connection with the Date table. I am completely stuck here.

Would you know a workaround that could help me ?

The final output should only be a percentage number per sales reps.

Thanks a lot !!

 

[Vander1981]

I was finally able to make it.

[CODEWeighted Ratio =
SUMX (
CALCULATETABLE (
ADDCOLUMNS(
VALUES ( 'Incentive calculation'[Product Group] );

"Sales"; CALCULATE ( SUM ( 'Incentive calculation'[NetSales Revenue] ) );

"Budget"; CALCULATE ( SUM ( 'Incentive calculation'[NetSales Budget] ) );

"Weight"; CALCULATE ( MIN ( 'Incentive calculation'[Weight]) )

);

'Incentivecalculation'[Parameter] = "Focus Product"

);

[Revenue] / [Budget]
* [Weight]
)
][/CODE]