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?
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
