Hi all,
I have a data base, dummy data attached. I need to calculate the over all target pacing for all the uni. numbers combined based on the interactions delivered and Guarantee. Over all target % is sum of all interactions /Guarantee.
The problem I am facing is, I need to consider only those interactions for overall target % which do not exceed the guarantee.
For example, for uni 'xyz' the total interactions are 900 and guarantee is 300. I only need to consider 300 interactions for 'xyz' to calculate overall target % and ignore the extra 600 because it is inflating the overall % achievement for all the unis. combined as they have clearly not reached the target.
I tried using running total but I don't know how to use it in a calculated field for LOD . The aggregated level calculation isn't giving the right answer.
Hi @nitin Pal Singh
Ok, I had another look. To get the 91.6% from 550/600 I suggest the following calculation :
WINDOW_SUM(IF SUM([Interactions]) > Max([Gaurantee]) THEN SUM([Gaurantee])ELSE
SUM([Interactions]) END ) / WINDOW_SUM((SUM([Gaurantee])))
If you need a single bar then I suggest you play around with hiding certain fields and headers as per below and attached :
Please see attached
Hope this helps
Antonio