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:
- 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')))
- 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!
