My data is a big flat file in Excel containing Sales figures.
| Category | Subcategory | Product | Sales | Budget |
| Beverages | Beers | Stella Artois | £20,081 | £62,800 |
| Beverages | Beers | John Smiths | £20,001 | £62,800 |
| Beverages | Beers | Guinness | £22,542 | £62,800 |
| Beverages | Cola | Cocacola | £11,583 | £23,300 |
| Beverages | Cola | Pepsi | £11,853 | £23,300 |
| Beverages | Wines | Hardys Stamp Shiraz | £10,959 | £36,500 |
| Beverages | Wines | Nicolas Feillatte Champagne | £11,838 | £36,500 |
| Beverages | Wines | Tio Pepe | £13,163 | £36,500 |
| Fruit | Bananas | Fairtrade bananas | £66,331 | £73,700 |
| Fruit | Oranges | Oranges | £21,464 | £66,300 |
| Fruit | Oranges | Satsumas | £22,136 | £66,300 |
| Fruit | Oranges | Ortaniques | £22,611 | £66,300 |
| Fruit | Pears | Williams | £75,878 | £69,300 |
| Fruit | Strawberries | Alba | £24,087 | £26,200 |
The Budget figures are aggregated at the level of Subcategory and are repeated in the transactions table for each instance of the Subcategory. I would like to make a table aggregated at the level of Category, like this.
| Category | Sales | Budget |
| Beverages | £122,020 | £122,600 |
| Fruit | £232,508 | £235,500 |
The Sales are easy but I can't work out how to calculate the Budget figures. I need a Measure that adds up the Budget figures for all Subcategories within the Category, but not weighting by sales. It's something like a Tableau INCLUDE. I've tried to use GROUPBY, but can't get it to work.
Please help!
