I've been throwing myself at this for a while to no avail and I would appreciate if you could give me any pointer!

The data set is very simple:

table

data_id, spend, phase_id, discount_percent

0, 100, 0, 10%

0, 100, 1, 15%

1, 123, 0, 5%

1, 123, 1, 6%

Etc.

What I'm trying to get is the "gross_spend" (OK) and especially the "discount_value" (NOK)

gross_spend is spend divided by 1 - the phase 0 discount, which I define as follows:

Code: Select all

`gross_spend:=CALCULATE(SUMX(table, DIVIDE([spend],1-[discount_percent])), table[phase_id]=0)`

contract_value is "gross_spend" times "discount_percent", which I define as follows:

Code: Select all

`contract_value:=SUMX(table, [gross_spend]*[discount_percent])`

Now what happens is when I place "phase_id" in columns in a pivot table, I will get the correct results for phase_id = 0, but nothing for phase_id = 1 except when the phase_id = 0 discount equals the phase_id = 1 discount. This behaviour seems so gimmicky to me and I can't seem to wrap my head around it.

I have uploaded a sample output, by filtering on relevant rows (to show you one case where the discounts are equal. The majority is otherwise). As you can see the gross_spend value is correctly repeated across phase_id but contract_value is null as soon as the discount_percent is different.

https://imgur.com/a/H24TU

Any idea what might be causing this? I suspect it has something to do w/ CALCULATE that I'm not seeing...

Many thanks!!