Split h worked from one specific Unit to several units

Emmeli — Fri, 09 Feb 2024 12:00:31 GMT

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

Re: Split h worked from one specific Unit to several units

barritown — Fri, 09 Feb 2024 21:09:38 GMT

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

Re: Split h worked from one specific Unit to several units

Emmeli — Sun, 11 Feb 2024 06:22:12 GMT

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%

Re: Split h worked from one specific Unit to several units

barritown — Sun, 11 Feb 2024 19:05:20 GMT

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

topic Re: Split h worked from one specific Unit to several units in DAX Commands and Tips

Split h worked from one specific Unit to several units

Re: Split h worked from one specific Unit to several units

Re: Split h worked from one specific Unit to several units

Re: Split h worked from one specific Unit to several units