SUM values in a table and group into categories

[sparker95]

I have a table called 'Costing' in this table I have columns 'Building & Work Stage ID' and 'Cost'. The ID column is a text column represent eahc project and then I have various values in the cost for each ID. I need to SUM the 'Cost' column for each ID creating a total project value. I then want to categorise these like I have attempted to below. However, I then want to create a table that provides a count of 'Building & Work Stage ID' for each 'Category'. When I try do this it just gives me a total count of IDs and all assigned to the £50m+ category.

Project Value £ Categories=

VAR ProjectCost = [Project Costs]

RETURN

SWITCH(TRUE(),

ProjectCost < 1, "No Data",

ProjectCost > 1 && ProjectCost <= 10000000, "£0-£10m",

ProjectCost > 10000000 && ProjectCost <= 30000000, "£10-£30m",

ProjectCost > 30000000 && ProjectCost <= 50000000, "£30-£50m",

"£50m+")

for reference

Project Costs =CALCULATE(SUM('Costing'[Cost]), VALUES('Costing'[Building & Work Stage ID]))
)

[sparker95]

 I have a table that includes these columns (this is  a simplified version).

Building & Work Stage ID	Cost
Project 1	1000000
Project 1	5000000
Project 1	3000000
Project 2	50000000
Project 2	600000
Project 1	100000
Project 2	10000000
Project 3	5000000
Project 3	10000000
Project 4	15000000
Project 4	10000000

I then want to sum for each project

	Total Project Value
Project 1	9,100,000
Project 2	60,600,000
Project 3	15,000,000
Project 4	25,000,000

Finally I want to create a count of projects within specific size ranges, as per below.

	Count of Project
£0-10m	1
£10-30m	2
£30-50m	0
£50m+	1

[lbendlin · Accepted solution]

[sparker95]

This works, thank you!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...