Get the Total Absolute Value Difference Based on Item Context (Not Total)

[former_member385923]

Hi WebI gurus,

Could you help me get the difference by individual DIM context?

For example (attached screenshot) samplexlsx.png

BO gives out the total difference of "-10" and not "50".

I've tried adding context by adding "in" but still with no luck.

Hope to hear your thoughts or suggestions.

Warm regards,

- Louis

[former_member385923 · Accepted solution]

=Sum(Abs([Value1] ForEach([Item]) - [Value2] ForEach([Item]) ))

[amitrathi239]

use below formula in the total row.

=Sum(Abs([Value1]-[Value2]))

[former_member385923]

Hi Amit,

Unfortunately, that would just give the "-10" in BO.
BO computes the totals of both Value1 and Value2 first and then subtracts them.

For example (you can try it in WebI), I've got a detailed report which includes the line items. Using the Sum(Abs([Value1] - [Value2]) ), it gives the correct absolute difference. But when you remove the line item dimension (using the same formula), it would give you the wrong absolute difference.

Cheers,

- Louis

[former_member385923]

Hi Amit,

Silly it didn't work before this works now.

=Sum(Abs([Value1] ForEach([Item]) - [Value2] ForEach([Item])
))

Cheers,

[amitrathi239]

are you expecting the total values in single cell without item object in the table?how many queries do you have?

if still it's not working then share some more details.