Forum Discussion
Transposing summed column values from one table into another table
Background
I have two tables.
Table 1 - called Pipeline - which is raw data extracted from another database showing the forecast revenue of several projects accross a number of months from current month to 12 months in the future.
| Opportunity ID# | Revenue Month1 | Revenue Month2 | Revenue Month3 | Revenue Month4 | Revenue Month5 | Revenue Month6 | Revenue Month7 | Revenue Month8 | Revenue Month9 | Revenue Month10 | Revenue Month11 | Revenue Month12 |
| OPP-1209404 | 0 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 | 25143 |
| OPP-1216857 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2500 | 2500 | 2500 | 2500 |
| OPP-976759 | 0 | 0 | 0 | 33083 | 33083 | 33083 | 33083 | 33083 | 33083 | 33083 | 33083 | 33083 |
| OPP-1029933 | 0 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 | 6268 |
| OPP-1240588 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 563 | 563 | 563 |
| OPP-1240590 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OPP-1137808 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OPP-1256376 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2625 | 2625 | 2625 | 2625 |
| OPP-1236171 | 0 | 0 | 0 | 0 | 0 | 0 | 9697 | 9697 | 9697 | 9697 | 9697 | 9697 |
| OPP-1126581 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OPP-841384 | 0 | 0 | 0 | 0 | 5357 | 5357 | 5357 | 5357 | 5357 | 5357 | 5357 | 5357 |
| OPP-996073 | 0 | 0 | 0 | 0 | 6250 | 6250 | 6250 | 6250 | 6250 | 6250 | 6250 | 6250 |
| OPP-1254549 | 0 | 18375 | 18375 | 18375 | 18375 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OPP-1237515 | 0 | 119 | 119 | 119 | 119 | 119 | 119 | 119 | 119 | 119 | 119 | 119 |
| OPP-1233047 | 0 | 8357 | 8357 | 8357 | 8357 | 8357 | 8357 | 8357 | 0 | 0 | 0 | 0 |
Table 2 - called Cashflow - has two columns "Month" which identifies the current month (today).
For the other column - "Forecast Cashflow" I want it to return the sum of each of the months across all projects. {the problem}
ie
Mar sum (revenue month 1)
Apr Sum (revenue month 2)
May Sum(revenue month 3) etc...
Month Forecast Cashflow
| Mar | |
| Apr | |
| May | |
| Jun | |
| Jul | |
| Aug | |
| Sep | |
| Oct | |
| Nov | |
| Dec | |
| Jan | |
| Feb |
From there I create a dynamic column chart based off various slicers.
Getting the summed monthly revenues transposed into the Cashflow table is where I'm stumped.
Appreciate any suggestions