Combined Lines on Table (or Matrix) that concatenates the values

[ergocorp]

Hi, I have a lot of data that somes requires grouping, and I'm trying to work out how to do it on Power BI.

It's a very large data set, so I'll give you an example of the parts I'm looking at for this challenge:

Some of the budget ui cells are combined, with the values being totalled up, so it looks like this:

FT62,ENG14	£      250,000.00
FT172,FT170,FT169,FT16,FT17	£      160,621.09
BP76	£  1,609,172.00

My forst port of call is to get them to add a unique ID to a line that let's it know if they are meant to be combined. My test data looks like this:

Project	Val	ID
IS18	£100.00	a
FT145	£200.00	a
BP70	£300.00	a
COP100	£400.00	b

So really when finished it should look like this:

Project	Val	ID
IS18,FT145,BP70	£600.00	a
COP100	£400.00	b

I've tried a matrix

I'm thinking there's got to be some sort of dynamic IF -> CONCAT statement in DAX. I say dynamic as I need it to total up and concatenate every line with the same ID.  I'm not sure how that would work.

The second problem is these lines have approvers indivdually. Going back to my inital sample data:

Project	Val	ID	Approver
IS18	£100.00	a	1
FT145	£200.00	a	1
BP70	£300.00	a	1
COP100	£400.00	b	2

However there are value thresholds, and when combined it goes further up the approval chain.

Project	Val	ID	Approver
IS18,FT145,BP70	£600.00	a	3
COP100	£400.00	b	2

I was thinking that a column  could state something like:

Approver (2) = IF(Val>=500,"3","1") etc.

Anyone got a solution to both of these?