2 Replies Latest reply: Mar 21, 2018 5:39 AM by Sofia Vaz

# Totals when working with % values

Hi all,

In a pivot, totals % are not working well... (picture attached)

Measure expression:

=if(getfieldselections(AllocTransfInsDate)>0,

count({<AllocTransfTransfStat-={"CANCEL"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat-={"CANCEL"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip,AllocTransfOrigMillCd)),

count({<AllocTransfTransfStat-={"CANCEL"},AllocTransfInsDateYear={"\$(=Year(Today()))"}, AllocTransfInsDateWeek={"\$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId) / sum(DISTINCT aggr(NODISTINCT count({<AllocTransfTransfStat-={"CANCEL"},AllocTransfInsDateYear={"\$(=Year(Today()))"}, AllocTransfInsDateWeek={"\$(=vCurrentInsDateWeek)"}>}AllocTransfAllocTransfId),AllocTransfOrigMktDescrip,AllocTransfOrigMillCd)))

Calculated dimensions:

1. Direction:

=if(match(AllocTransfOrigMarketCd,'GPS','26', '10','11','12','13','14','15','17','22','23','24','25','28','29','93','94','93A','PK'), 'Europe',

if(match(AllocTransfOrigMarketCd, '46'),'USA',

if(match(AllocTransfOrigMarketCd,'16','92','92Z','92A','92M','92L','92O', '16I'), 'International',

'Other')))

1. Office:

=if(match(AllocTransfOrigMarketCd,'14','15','93'),'AU+CE+CH',

if(match(AllocTransfOrigMarketCd,'22','23'),'BL+HL+NE',

if(match(AllocTransfOrigMarketCd,'999','AC','AM','AP','OF','27','.','SET','30','PK','73','GPS','SP','SCO'),'Other',

if(match(AllocTransfOrigMarketCd,'91','92','92Z','92L','92M','92O','91Z','91L','91M','91A','91O','92A'),'Overseas',

if(match(AllocTransfOrigMarketCd,'17','28'),'UK+IR',

if(match(AllocTransfOrigMarketCd,'92Z','26','12','13','25','16I','24','94','93A','10','29','11','16','46'),AllocTransfOrigMktDescrip))))))

Does anyone know what I might be doing wrong?

Thank you!

• ###### Re: Totals when working with % values

Can you share sample app or sample data to see whats wrong with the formula?

You may try this without equal sign, not sure though -

AllocTransfInsDateWeek={"\$(=vCurrentInsDateWeek)"}

• ###### Re: Totals when working with % values

Thank you Digijay. I've tried without the = sign but the output remains the same.

I'm not able to share sample data...