I have a chart with two dimensions, Item Number and Batch Number. It also has 3 expressions: sum(WI_TotalUnits), sum([Physical stock]), and the 3rd expression is a conditional statement intended to count the number of lines on which there is no variance between WI_TotalUnits and Physical stock, like so:
if (sum([Physical stock]) - sum(WI_TotalUnits) <> 0,0,1)
I then use the Avg function (instead of Expression Default) to convert to the percentage of rows that have no variance.
However my problem comes in when I want to do the same percentage calculation in a TEXT BOX, rather than in the totals row of my chart. I need to place the entire logic into an independent text box to get the same percentile result. I have been looking at the AGGR() function but this is giving me the wrong answer. This is what I have so far:
sum(
aggr(
if(sum(WI_TotalUnits - [Physical stock]) <> 0,0,1)
,[Item number], [Batch number]
)
)
This aggregate gives a percentage result that is different to the one in the original chart. What am I doing wrong?
Thanks!
