Split h worked from one specific Unit to several units

[Emmeli]

Hi

I would like to Split "hours worked" marked with registered on Unit 1 in to several new rows.

This is the data I have

Owner		Hours worked	Date		Object_1		Object_2
Person 1		8	2024-01-01		Unit 1		Development work
Person 2		8	2024-01-01		Unit 1		Administration
Person 3		8	2024-01-01		Unit 1		Development work
Person 4		8	2024-01-01		Unit 2		Administration
Person 5		8	2024-01-01		Unit 3		Administration
Person 6		1,5	2024-01-01		Unit 4		Traveltime
Person 6		4	2024-01-02		Unit 1		Development work
Person 1		3	2024-01-02		Unit 1		Development work
Person 2		8	2024-01-02		Unit 2		Controlling
Person 3		8	2024-01-02		Unit 3		Development work
Person 4		1	2024-01-02		Unit 4		Administration
Person 5		2	2024-01-03		Unit 1		Development work
Person 3		1	2024-01-03		Unit 2		Traveltime
Person 4		1	2024-01-03		Unit 2		Development work
Person 5		2	2024-01-03		Unit 3		Administration
Person 1		5	2024-01-03		Unit 4		Administration

I would like to split the hours worked according to below percentage 

Unit 2 10%

Unit 3 10%

Unit 4 10%

Unit 5 20%

Unit 6 20%

Unit 7 30%

The information in column "owner", "date", "Object_1" and "Object_2" will remain the same.

After splitting the row, erase all raws registered in Unit 1.

Creating this

Owner		Hours worked	Date		Object_1	Object_2
Person 1		8		2024-01-01		Unit 1	Development work
Person 1		0,80		2024-01-01		Unit 2	Development work
Person 1		0,80		2024-01-01		Unit 3	Development work
Person 1		0,80		2024-01-01		Unit 4	Development work
Person 1		1,60		2024-01-01		Unit 5	Development work
Person 1		1,60		2024-01-01		Unit 6	Development work
Person 1		2,40		2024-01-01		Unit 7	Development work
Person 2		8		2024-01-01		Unit 1	Administration
Person 2		0,80		2024-01-01		Unit 2	Administration
Person 2		0,80		2024-01-01		Unit 3	Administration
Person 2		0,80		2024-01-01		Unit 4	Administration
Person 2		1,60		2024-01-01		Unit 5	Administration
Person 2		1,60		2024-01-01		Unit 6	Administration
Person 2		2,40		2024-01-01		Unit 7	Administration
Person 3		8		2024-01-01		Unit 1	Development work
Person 3		0,80		2024-01-01		Unit 2	Development work
Person 3		0,80		2024-01-01		Unit 3	Development work
Person 3		0,80		2024-01-01		Unit 4	Development work
Person 3		1,60		2024-01-01		Unit 5	Development work
Person 3		1,60		2024-01-01		Unit 6	Development work
Person 3		2,40		2024-01-01		Unit 7	Development work
Person 6		8		2024-01-01		Unit 2	Administration
Person 5		8		2024-01-01		Unit 3	Administration
Person 4		1,5		2024-01-01		Unit 4	Traveltime
Person 6		4		2024-01-02		Unit 1	Development work
Person 6		0,40		2024-01-02		Unit 2	Development work
Person 6		0,40		2024-01-02		Unit 3	Development work
Person 6		0,40		2024-01-02		Unit 4	Development work
Person 6		0,80		2024-01-02		Unit 5	Development work
Person 6		0,80		2024-01-02		Unit 6	Development work
Person 6		1,20		2024-01-02		Unit 7	Development work
Person 1		3		2024-01-02		Unit 1	Development work
Person 1		0,30		2024-01-02		Unit 2	Development work
Person 1		0,30		2024-01-02		Unit 3	Development work
Person 1		0,30		2024-01-02		Unit 4	Development work
Person 1		0,60		2024-01-02		Unit 5	Development work
Person 1		0,60		2024-01-02		Unit 6	Development work
Person 1		0,90		2024-01-02		Unit 7	Development work
Person 2		8		2024-01-02		Unit 2	Controlling
Person 3		8		2024-01-02		Unit 3	Development work
Person 4		1		2024-01-02		Unit 4	Administration
Person 5		2		2024-01-03		Unit 1	Development work
Person 5		0,20		2024-01-03		Unit 2	Development work
Person 5		0,20		2024-01-03		Unit 3	Development work
Person 5		0,20		2024-01-03		Unit 4	Development work
Person 5		0,40		2024-01-03		Unit 5	Development work
Person 5		0,40		2024-01-03		Unit 6	Development work
Person 5		0,60		2024-01-03		Unit 7	Development work
Person 3		1		2024-01-03		Unit 2	Traveltime
Person 4		1		2024-01-03		Unit 2	Development work
Person 5		2		2024-01-03		Unit 3	Administration
Person 1		5		2024-01-03		Unit 4	Administration

[barritown · Accepted solution]

Hi @Emmeli,

Try this piece of code:

Result Table = 
    UNION ( FILTER ( Data, [Object_1] <> "Unit 1" ),
            SUMMARIZE (
                ADDCOLUMNS ( 
                    CROSSJOIN ( 
                        FILTER ( Data, [Object_1] = "Unit 1" ), 
                        Rules ),
                    "Hours worked upd", [Hours worked] * [Percent] ), 
                [Owner], [Hours worked upd], [Date], [Unit], [Object_2] ) )

Data is your fact table, Rules is another auxiliary table which contains units and percentage related to them (check the attached PBIX if needed).

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

Hi @Emmeli,

Try this piece of code:

Result Table = 
    UNION ( FILTER ( Data, [Object_1] <> "Unit 1" ),
            SUMMARIZE (
                ADDCOLUMNS ( 
                    CROSSJOIN ( 
                        FILTER ( Data, [Object_1] = "Unit 1" ), 
                        Rules ),
                    "Hours worked upd", [Hours worked] * [Percent] ), 
                [Owner], [Hours worked upd], [Date], [Unit], [Object_2] ) )

Data is your fact table, Rules is another auxiliary table which contains units and percentage related to them (check the attached PBIX if needed).

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Emmeli]

It worked really weel, thank you.

But I realised that i would like to be able to add that Person 6 will be split according to these separate rules.

Unit 2 50%

Unit 3 10%

Unit 4 10%

Unit 5 10%

Unit 6 10%

Unit 7 10%

And Person 5 according to these.

Unit 2 30%

Unit 3 10%

Unit 4 10%

Unit 5 20%

Unit 6 20%

Unit 7 10%

[barritown · Accepted solution]

In this case, I'd create an additional table with the columns Owner, Object_1 and Percent and start with a merge in Power Query (Left Join on Owner). After there will be a need to create a couple of conditional columns and perform some deletions and renamings - please see the attached file and follow the Power Query steps.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian