cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Aggregating budget values at the correct level

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.

1 ACCEPTED SOLUTION
Super User

@GideonM  use this as a measure

``Measure 3 = sumx(SUMMARIZE(t1,t1[Category],t1[Budget]),t1[Budget])``

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
2 REPLIES 2
Super User

@GideonM  use this as a measure

``Measure 3 = sumx(SUMMARIZE(t1,t1[Category],t1[Budget]),t1[Budget])``

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
New Member

Thanks @smpa01 that looks great!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.