Total of all rows in pivot

[Aspiring_Developer]

Hello All,

I have below table:-

I have to add a new column named 'Average ' using below calculation :-

Ethernet - issueno  (15+4)= 19 -- this is total

Open wkg days (76+88)= 164-- this is total

average - 15+19 / 76+164- new column to be added

Can anyone please help me how we can do it ?

Thanks in advance

@sunny_talwar  @Kushal_Chawda 

[brunobertels · Accepted solution]

Hi

may be this :

aggr(count ( Total<CATEGORY> ISSUE_NO),CATEGORY) 

/

aggr(count(TOTAL<CATEGORY> OPEN_WKG_NO),CATEGORY)

[Aspiring_Developer]

Hello @brunobertels 

Thanks for your quick response.

I have create below expression:-

aggr(sum(OPEN_WKG_DAYS),CATEGORY) (numerator)

aggr(count(ISSUE_NO),Category_EMS) (demoninator) - giving incorrect results

For numerator (73+72) = 145 which is correct

But for denominator (18+3)=21 should come, but is giving me 527 because of the null values present in dimensions.

I tried to supress my null values in backend like below but still in my denominator it is taking those values :-

Can you please help ? Not sure , how should I exclude null values from Count expression.

Thanks in advance

[brunobertels]

Hi 

first check that option exclude null value in the dim of your cross table are empty 

Then try in your load script to replace 

not match(CATEGORY,'-') by not isnull(CATEGORY)  also replace 

not match(AGE_BAND_WKG,'-') by not isnull(AGE_BAND_WKG)