RangeSum with 3 Dimension

[qv_jagan]

Hi,

I have below data. Column 5 is the expected value required.

I tried using RangeSum it is not working.

Dimension: Column 1-4

Expression 1: Sum(Value)

Expression 2: RangeSum(Above(Total Sum(Value),0,RowNo(TOTAL)))

Can some please help.

Country	State	Branch	Value	Expected Value
C1	S1	B1	1	1
C1	S1	B2	2	3
C1	S2	B3	3	6
C2	S4	B4	4	4
C2	S5	B5	5	9
C3	S5	B6	6	15

[rubenmarin]

Hi Jagan, I created a document and it's working with your original expression, what isn't working for you?.

Are you doing this in a pivot table or in a straight table?

[rubenmarin · Accepted solution]

Hi, I spotted the difference, now I have the same doubt as Manish, why that 15? shouldn't be '6' (it's a new country so it starts again)?

I attach a new document with this behaviour, I used a hidden column to calculate how many rows of the same country has to sum the above() function.

[qv_jagan]

That was a typo. Sorry for inconvenience.

This is what I was expecting.

Can you please explain how these 2 expression works.

RangeSum(Above(TOTAL Sum(Value), 0, Cont)) ---------------------> Correct (RangeSum Grouped at Country Level)

RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))) -----> Full Accumulation Not Required.

Thank you so much for your help.

[rubenmarin]

Hi Jagan,

RangeSum(Above(TOTAL Sum(Value), 0, Cont)

Cont is another expression on the chart, it's an incremental autonumeric that starts at 1 each time it founds a different country than the previous row, so each row knows how many rows of the same country are above (those rows have to be accumulated)

This column is hidden in the presentation tab, so it's only used to calculate another expression, not to show in chart.

RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL)))

RowNo(TOTAL) counts the number of rows, the TOTAL identifier specifies to ignore chart dimensions, so it counts all rows, without restarting in each country.

You can add an expression with just RowNo(TOTAL) and another with RowNo() to check the differences. The behaviour also changes between a straigh table and a pivot table.

Regards.