I have calculated an KPI with the following formula:
Sum(Aggr(If(Sum([IstAEzuFP 2018]) > 0, Sum([BudgetzuFP 2018])),AccountingMonth))
So the formula perfectly works in the KPI.
But if I put the formula in an Pivot table to distinguish between products it does not work at all:
Does anyone has an idea how I can make the formula work also in the pivot!?
it is "better" now. I Added the dimension "system" in the aggr function an I got values (see screenshot). But those values are not correct yet.
If I do an filter on the System (SIPROTEC for example), the values are changing to the correct numbers!
But I would like to see the correct numbers already in the total pivot overview.
Do you have any idea why that is not working?
Thank you so much!
It's difficult to say what didn't work. Maybe it's not enough to add System within the aggr() else it might be the wrong field or there are additionally dimensions needed. Depending on your datamodel it might be also necessary to use a NODISTINCT statement within the aggr() or a TOTAL statement within the outer aggregation or similar.
For testing I would further split the two expression parts - rangesum(abvove()) und aggr() - to be sure that each part returned the expected values.
Thank you! Just found out, that the aggr. function is working proberly!
The problem is the rangesum part of the formula.
As soon as I select a System (e.g. SIPROTEC) in the Pivot the amount of the rangesum changes to the correct value, before selecting a system the rangesum shows a wrong value. (see screenshot)
Thank you for any help!