Pivot table total is incorrect for expression which is based if first exp is 0 then exp2.

[miranrai]

I have a pivot table with one expression as:

If Expression1 = 0 or null then Expression2 else Expression1.

This is working perfectly fine but when it comes to total on the top its giving incorrect values as its evaluating only Expression1. My requirement is to get the sum of  Expression2 and Expression1.

Any help please?

[lironbaram]

hi 

your expression work fine as it's evaluating the expression in the total row base on the whole data set 

it's not doing sum of rows 

if you want sum of rows 

you can do 

sum(aggr(if(Expression1=0,Expression1,Expression2),Dimension1,dimension2,etc..))

where instead of Dimension1 etc , you need to use the dimensions of the pivot table 

[miranrai]

Thanks for your reply @lironbaram 

I tried sum(aggr (..) in my expression but its giving me 0 value in total (I tried this expression in KPI). My actual expression  in report is:

vRC is currency variable.

Sum(Aggr(if(
Sum({<DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)=0
or IsNull(Sum({<DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)),

sum([DM_GC.clean_value_gc])*$(vEXRateRC),

Sum({< DM_GC_RC.reporting_currency={'$(vRC)'}>}
DM_GC_RC.published_valuation_rc_amount)),
,[DM_RESULTS.security_group],[DM_RESULTS.instrument_type],[DM_RESULTS.GPV_Gross_Investment]))