Compute product wise YTD Revenue from a matrix like/Cross tabular dataset

{ 0 Comments }

Assume the following data layout

Name Budget April Actual April Budget May Actual May Budget June Actual June
a 2 1 1 4 3
b 4 4 2 1
c 2 3 3 3
d 2 1
e 5 6 4 8 6

As one can observe here, there are two sub columns for each month – Budget and Actual.  From this data layout, we wish to compute the Product wise YTD budget and Actual sales figure.  So for example, if one selects May in a drop down, then the YTD budget for Product B should be 6 and YTD actual for Product B should be 5.

The expected solution should look like this

Month Name May
Products YTD Budget YTD Actual
a 2 2
b 6 5
c 2 3
e 9 14
Grand Total 19 24

You may download my solution workbook from this link.

You may watch a short video of my solution here

Leave a Comment

Your email address will not be published.

*