Variable calculates nothing when I remove a certain field

[Melissa2]

Hi guys

I am hoping that any of you can assist.  I have search quite a few topics but just could get a solution.

I have a variable which i defined as follow:

if(AccrualType = 'REB', sum(If( [Calculation type]= 'Value', QtyInvoiced * PromotionPercentage, NetSalesValue * PromotionPercentage/100)))

The AccrualType field consist of 6 different types of which REB is one of them.  Calculation type can either be Value or Percentage.

I used a straight table to test all formulas before I start with KPI etc.

The problem I am facing is that the variable calculates perfectly if I add my PromotionPercentage in my table, but as soon as I remove it then everything greys out.

See screenshots below - 1st is where PromotionPercentage is not part of the table and 2nd one I added that field.

Could it be because i have multiple % connected to that code and if so, how can I resolve it.

Thanking in advance.

[rubenmarin]

Hi, it looks like a model isse, there is no relation between an invoice line and a promotion percentage, so each row is assigned to each promotion percentage.

In your 2nd image the turnover is 5850, and that value is applied the 7%, 2%... do you really want the sum of this value by each percentage?

Maybe with: if(AccrualType = 'REB', sum(If( [Calculation type]= 'Value', QtyInvoiced, NetSalesValue/100))*sum(* PromotionPercentage))

Or: if(AccrualType = 'REB', sum(aggr(sum(If( [Calculation type]= 'Value', QtyInvoiced * PromotionPercentage, NetSalesValue * PromotionPercentage/100)),PromotionPercentage)