How to show SUM on KPI card instead of default expression?

[jacek27031]

I am calculating custom expression in the KPI card as below and the sum numbers are wrong. 

(if(([BP2]='XXX'),
Sum(
RangeMax(
([# STI Plan] * (([Cprice] - ([XXX CDF] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))),0)),

if(([Front]='1'),
Sum(
RangeMax(
([# STI Plan] * (([Cprice] - ([SRP] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))),0)),
Sum(
RangeMax(
([# STI Plan] * (([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP))))),0)))))

How to achieve the Sum Total value in KPI. Also, is there any other way to write below expression by removing If conditions so that it will show correct value in KPI?

Thanks in advance for any tips! 

[stevedark · Accepted solution]

Hi @jacek27031 

This could all perhaps be tidied up quite a bit in the load script, as described on your other post.

In order to make it work as is you will need to use an AGGR function. This works by creating a virtual table and then performing a calculation over that virtual table.

If you get your expression giving the correct result in a table, with a number of dimensions, you then need to pass those dimensions as parameters into the AGGR function. At the very least you will need to reference BP2, e.g.

sum(aggr(... your expression here ..., BP2))

I expect that other fields will be required, in a comma separated list, following BP2.

Hope that helps.

Steve