Filtering duplicates when splitting column into rows

[ChrisR22]

Hello, 

I have a dataset similar to the following

Project ID	Owner	Category	Hours
1	Adam	Apple, Orange, Pear	5
2	Susan	Carrot	12
3	John	Orange, Carrot	3
4	Adam	Apple, Carrot	7

In order to create visuals based on the category, I have to split the data rows by delimeter, resulting in the following

Project ID	Owner	Category	Hours
1	Adam	Apple	5
1	Adam	Orange	5
1	Adam	Pear	5
2	Susan	Carrot	12
3	John	Orange	3
3	John	Carrot	3
4	Adam	Apple	7
4	Adam	Carrot	7

However, this makes it difficult to create a visual based on the owner data and hours. 

I am looking to create a chart that shows the breakdown of hours per owner, but disregarding the duplicate values and only counting on a per project basis.

Ex. When looking at Adam's total hours it should be 5+7 =12. And when looking at total hours spent on Carrot it should be 12+3+7 = 22. 

If anyone has any insight into this please let me know, thanks!

[Anonymous · Accepted solution]

Hi @ChrisR22 ,

Depending on the information you provided, you can follow these steps below:

1.Add an index column.

2.Add new column.

OwnerChosen = 
VAR _rank =
    RANKX (
        FILTER ( 'Table', 'Table'[Project ID] = EARLIER ( 'Table'[Project ID] ) ),
        'Table'[Index],
        ,
        ASC
    )
RETURN
    IF ( _rank = 1, 'Table'[Hours] )

3.Add new measure.

Total = 
IF (
    ISFILTERED ( 'Table'[Owner] ),
    SUM ( 'Table'[OwnerChosen] ),
    SUM ( 'Table'[Hours] )
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.