7 Replies Latest reply on Apr 25, 2018 8:03 PM by donli.lv

Showing sum of abs % in grand total for calculated field

hey guys, i have a question of achieving below calculation, i tried multiple ways, but cannot get what i expected.

the % is calculated by ABS((sum([Sch])/(total(sum([Sch]))/4)/4)-(sum([Req])/(total(sum([Req]))/4)/4)), it's basically the value for each block divided by column total, and comparing the difference between req and sch.  sometimes req is higher, sometimes sch is higher.  what i am going to achieve is something like a SUM in excel for the % column, of the absolute values of %.  but even if i added ABS in the calculated fields, it returns 0% rather than 3.38% as you can easily sum five % numbers together.  not sure what's wrong here.

• 1. Re: Showing sum of abs % in grand total for calculated field

Hi, Donli

Is there any sample workbook can be shared so that it will be easier for people to help

ZZ

• 2. Re: Showing sum of abs % in grand total for calculated field

Hey Donli,

Have you tried this option.

In this below chart, for Percentage are not Summing.

To get sum of Percentage, we need to right click on Calculation1-> then Select Total Using(Automatica)-Select Sum.

You will get this output.

• 3. Re: Showing sum of abs % in grand total for calculated field

thanks Venkat, i've tried this, but for my calculated field, the option was grayed out.  it works if the calculated field was simply sum[a]/sum[b], but mine is something like sum[a1]/total(sum[a]), it takes grand total as denominate.  it's something like 391,049/2,297,201 for the first row in your sample data, and 363,952/2,297,201 for the second row.  when i pull the field to measure values, it just doesn't show AGG...  not sure how to do sum for this kind of % calculation.

• 4. Re: Showing sum of abs % in grand total for calculated field

Cna you please share Fake Data with .twbx file for better help.

• 5. Re: Showing sum of abs % in grand total for calculated field

here's the sample tbwx, thanks!

• 6. Re: Showing sum of abs % in grand total for calculated field

Hi, Donli

Hope this helps

ZZ

• 7. Re: Showing sum of abs % in grand total for calculated field

thanks Zhouyi, it works.