Divide values from other rows

Q&A
,
21

Hi @consultantcon,

Use of avgOver with ID field in the calculation doesn’t require that you include ID in the visual.

It is not clear from all the prior posts what exactly you are trying to do. (since all that is already in context of specific functions)
That is why I’m asking for you to provide the intermediate and final expected values based on above data sample. Also provide the calculation used in simple math terms. We will then figure out how best to achieve same with Quick Sight calcs.

Regards,
Arun Santhosh

22

Hi Arun,

the expectation
X axis - Year
Y axis - percentage of activity (number of activity / expected value based on parameter)
Legend/Color - activity
this would be the line chart

with avg value column not avgOver
image

it should average ID and sum it not average overall the value

Thanks

23

Hi @consultantcon ,

Calcs given below get the desired output.
Avg Value by ID (split across rows) : avgOver(Value,[ID],PRE_AGG) / countOver(1,[ID],PRE_AGG)
Sum of Selected Activity’s ID level Averages : sumOver(ifelse(Activity=${pActivity},{Avg Value by ID (split across rows)},NULL),[],PRE_AGG)
Percent Calc : sum(Value)/min({Sum of Selected Activity's ID level Averages})

image
image1796×954 76.6 KB

You will probably need to add year to partitions based on how you want the calcs to be done.

Regards,
Arun Santhosh

24

Hi Arun,

Thanks for your time to doing this, but somehow when I applied your calculation seems it’s giving me different value for Avg Value by ID (split across rows)?
image

while avg value is avg(Value,[ID])

this reference I copied your calculation inside but not sure why the result is different
image

may I know if I did something wrong with it? I just copied your calculation into mine with the same data I gave it to you

25

Hi @consultantcon,

Check the aggregate applied in the field well for that field.
The default that will get applied is min. You need to change that to sum to get the display right (ie - if you want to see the intermediate calc )
This doesn’t impact the final calc as we are doing an explicit sum while calculating Sum of Selected Activity’s ID level Averages.

Regards,
Arun Santhosh