I am trying to create a KPI from a table. The table looks like this:
ID Contract PIID TO Obl
1 273 - - $3000
2 - 39 - $5400
2 - 39 T36 $5000
2 - 39 T45 $400
2 - 32 - $500
2 - 32 S32 $100
2 - 32 S35 $400
3 467 - - $700
I have filters for the Dimensions: ID, Contract, PIID, TO. When a selection is made within the filters I want to trigger the KPI.
The KPI would be the sum of Obl by ID. The issue I have is the aggr when there are multiple lines for an ID. For ID 2 I want to get the sum of just the entries where Contract IsNull and TO IsNull. Not the other rows within the corresponding ID.
I have tried for the KPI:
=Sum(Aggr(If (not IsNull ([Contract]), Obl,
If(not IsNull(TO), Obl, If(IsNull([Contract]) and IsNull([TO]), Obl))), ID))
It works when there are not multiple entries for a filter. Any help would be appreciated.