How to Display KPI value matching to Sum Total instead of Expression Total

[polisetti]

Hi Team,

I am calculating custom expression in the table chart containing different dimensions as below and on selecting expression total in the chart the numbers were wrong and it is giving correct result on Sum total. 

My requirement is, I need to show this metric value in KPI and it will display Expression total by default. 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

=if(Sum([Recoverable])=0, Sum({<Set analysis>}Amount),
if(Sum([Minimum Recoverable])>0,
(Sum({<Set analyis}>}Amount)-Sum({<Set Analysis}>}[Recoverable]))))

Regards,

Polisetti

[chrismarlow · Accepted solution]

Hi,

I think you need to use AGGR, so setting aside your set analysis & I don't know what your dimensions are, so have just put one in called 'Dim', something like;

sum(if(aggr(Sum([Recoverable]),Dim)=0, aggr(Sum(Amount),Dim),
if(aggr(Sum([Minimum Recoverable]),Dim)>0,
(aggr(Sum(Amount),Dim)-aggr(Sum([Recoverable]),Dim)))))

Giving;

You would put your set analysis back in & a comma delimited list of the fields to aggregate over for AGGR (check the docs for more details).

Do share a few more details if this does not help.

Cheers,

Chris.

[polisetti]

Hi Chris,

Thank you for your reply. They are multiple dimensions in my case and I just added the lowest granularity dim in the KPI by using the Aggr function. 

I am getting results as expected but need to verify thoroughly as the expression contains lot of condition set analysis. 

Regards,

Jaswanth