Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon, i am struggling getting the % calculated using the AGGR(NODISTINCT Function in my pivot table.
Our distribution can be by Sea or Air (more options in the future) and our target is to have the distribution 99% by sea, so ofcourse we want to see the % of each distribution for a certain period.
as seen in this table:
However the calculation to get our Yards invoiced is:
if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0)
Somehow i am unable to calculate the % as seen in the table above using the AGGR(NoDistinct function, could anyone please help me with the logic of this calculation.
May be try like this:
Sum(Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))
/
Sum(TOTAL <[Reporting Group], Distribution>Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))
May be try like this:
Sum(Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))
/
Sum(TOTAL <[Reporting Group], Distribution>Aggr(if(sum({<[Reference Number Qualifier] ={'CC'}>} replace([Reference Number],'.',',')*1),sum({<[Item Structure]={'FAB'} >} [Yards Invoiced]),0), [Reporting Group], Distribution, [Mode of Transport]))
Thank you so much, it worked right away and i also found out what i did wrong.