Running Sum on Two Dimension object

[Former Member]

Dear All,

I have below scenario:

In Crosstab:

Row: Region,State

Column : Month

Measure - Sales & Sales % (= Sales for State/Total Sales for that Region)

Expected Output:

Region - Month --- Jan Sales -Jan % - Feb - Feb % - Mar - Mar %

West - State -1 -- 10 - 66%(10/30) - 40 - 66% (40/60) - 50 - 50%(50/100)

West -State -2 -- 20 - 34% (20/20) - 20 - 34% (20/60) - 50- 50% (50/100)

West Running Total 30 - 100% - 60 -100% - 100 -100%

East - State -1 -- 0 - 0%(0/0) - 60 - 100% (60/60) - 50 - 50%(50/100)

East -State -2 -- 20 - 100% (20/20) - 0 - 0% (0/60) - 50- 50% (50/100)

West Running Total 20 - 100% - 60 -100% - 100 -100%

Actual Output:

Month --- Jan Sales -Jan % - Feb - Feb % - Mar - Mar %

West - State -1 10 - 66%(10/30) - 40 - 57% (40/70) - 50 - 35%(50/140)

West - State -2 20 - 34% (20/20) - 20 - 22% (20/90) - 50- 26% (50/190)

Running Total 30 - 100% - 90 (304030) -79% - 100 -61%

East - State -1 -- 0 - 0%(0/0) - 60 - 75% (60/80) - 50 - 35%(50/140)

East -State -2 -- 20 - 100% (20/20) - 0 - 0% (0/80) - 50- 27% (50/180)

West Running Total 20 - 100% - 80 -75% - 180 -57%

My Running Total Formula is : = Runningsum( Sales; (Region))

Sales % = Sales / Running Total Formula

In short, I want to reset running total while my state and Month gets change. Here what's going on is while my state will get change, sum will reset and if I use Month in Running Sum formula I will get reverse effect in State.

Can anyone have idea how to solve this issue ?

Any answer would be appreciate.

Regards,

Rishit

[Former Member · Accepted solution]

hi Rishit,

You need not create any formulas to achieve the expected output.

Create a cross tab with

row -> region, state

column -> month

measure -> sales

Apply a break on Region

Now select sales column

1) insert sum using Summary(Sigma) button.

2) insert Percentage using Summary button

3) Remove unrequired row i.e. horizontal rows for Percentage. But keep the Percentage Column

4) Manually you can type 100% on right hand side of sum.

Regards,

Vamsee

[Former Member]

Use =Sum(<Sales> In Body) In (<Region> , <Month>)

This will make sure you can use this in other formula statements to calculate % for example and it wont loose its context.