How to do Indexing for table calculation?

Question & Answer
,
1

image
image2448×1792 281 KB

I have a table calculation “Percent of Total” which gives me correct values for individual rows and even the average (using show totals).
Now, I want to Index each row against the average.

Ex:
45.4-48.0 = -2.6 for Gender Woman for row 1
55.1-48.0 = 7.1 for Gender Woman for last row before total.

54.6 - 52.0 = 2.6 for Gender Man for row 1
44.9 - 52.0 = -7.1 for Gender Man for last row before total.

Basically the aim to figure out if each row is below average or above average.

I was able to create a calculation for total average using LAC
sum({cnt_members},[community, gender]) and then doing percent of total table calculation… but when I do

sum({cnt_members},[{Title, Season},community, gender]) - sum({cnt_members},[community, gender])

I get The Level Aware Calculation (LAC) aggregate functions inside one visual aggregate functions should always share the same grouping key.

Any suggestions on how can I get index against average?

2

Hi can you try using pre_agg calculations?

sumOver({cnt_members},[{Title, Season},community, gender],PRE_AGG) - sumOver({cnt_members},[community, gender],PRE_AGG)

3

Hi @Max ,

Thank you for the response. Your calculation does resolve the LAC error I was getting, but it still doesn’t provide the difference of a row against average (Indexing).

image
image2242×1798 308 KB

4

Are you saying you want sum minus average?

sum(sumOver({cnt_members},[{Title, Season},community, gender],PRE_AGG)) - avg(sumOver({cnt_members},[community, gender],PRE_AGG))
5

Hey @Max ,

Since I needed it % format and not raw numbers… Here’s the calculation I used…

sumOver(sum({cnt_members}),[{Title, Season},community, gender],POST_AGG_FILTER)/sumOver(sum({cnt_members}),[{Title, Season},community],POST_AGG_FILTER)
-
sumOver(sum({cnt_members}),[community, 
gender],POST_AGG_FILTER)/sumOver(sum({cnt_members}),[community],POST_AGG_FILTER)

Appreciate your response though!