topic Re: SUM values in a table and group into categories in DAX Commands and Tips

SUM values in a table and group into categories

sparker95 — Thu, 22 Aug 2024 16:09:13 GMT

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]))
)

Re: SUM values in a table and group into categories

lbendlin — Fri, 23 Aug 2024 00:28:02 GMT

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-Forum/ba-p/963216

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/1447523

Re: SUM values in a table and group into categories

sparker95 — Fri, 23 Aug 2024 12:27:08 GMT

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

Re: SUM values in a table and group into categories

lbendlin — Fri, 23 Aug 2024 12:58:56 GMT

Re: SUM values in a table and group into categories

sparker95 — Wed, 28 Aug 2024 07:54:08 GMT

This works, thank you!