cancel
Showing results for
Did you mean:
New Member

## spliting specific values based on the values of two columns and add to other rows

Hi,

I have a table with three columns: date, workcenter and SpentHours

 Date Workcenter SpentHours 01/01/2020 100.DTC 30 01/01/2020 100.d102 100 01/01/2020 100.D103 150 01/01/2020 100.D202 200 01/01/2020 100.DFT 20 01/01/2020 100.DPT 10

I need to divide the hours of specific workcenter and add them to others based on the date column .So for example, for a specific date (01/01/2020) I need to :

1. split the 20 hours of workcenter 100. DFT in 3 parts  and add to workcenter 100.D102 two-thirds and one-third to 100.D103

2. add the hours of workcenter 100.DPT to workcenter 100.D102

3. split the hours of workcenter 100.DTC in 3 parts and add to workcenters 100.D102, 100.D103 and 100.D202 one-third each

And of course I need to replicate these splits to other days in the Date column

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: spliting specific values based on the values of two columns and add to other rows

Hi @pfz ,

You can follow the below steps to achieve it:

1. Create a rule table "Splits"

2. Create a calculated column with below formula:

``````Column =
var _DFT=CALCULATE(MAX('Splits'[DFT]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DPT=CALCULATE(MAX('Splits'[DPT]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DTC=CALCULATE(MAX('Splits'[DTC]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DFTSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DFT"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
var _DPTSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DPT"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
var _DTCSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DTC"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
return IF('Spent hours'[Workcenter] in {"100.DFT","100.DPT","100.DTC"},BLANK(),'Spent hours'[SpentHours]+_DFT*_DFTSH+_DPT*_DPTSH+_DTC*_DTCSH)``````

Best Regards

Rena

3 REPLIES 3
Highlighted
Super User III

## Re: spliting specific values based on the values of two columns and add to other rows

Hey @pfz ,

here is my approach.

First I created a table "AllocationRules" that describes how Spenthours of Workcenter (Sender) will be split or allocated to other Workcenter (Receiver).
To avoid rounding issues I think in parts.

Inside the measure Allocation there is a variable that sums all the parts and creates a fraction.
If not 100% of Hourspent will be allocated, a row with an empty receiver can be used to configure this.

The measure Allocation (in my example I named the table "Fact"):

``````Allocation =
var t =
SUMMARIZE(
'Fact'
, 'Fact'[Date]
, 'Fact'[Workcenter]
)
return
SUMX(
t
, var __workcenter = 'Fact'[Workcenter]
var __date = 'Fact'[Date]
var allsender =
CALCULATETABLE(
FILTER('AllocationRules' , 'AllocationRules'[Receiver] = __workcenter)
, ALL(AllocationRules[Sender])
)
var noOfReceivings = COUNTROWS(allsender)
return
IF(noOfReceivings > 0
, SUMX(
allsender
, var __sender = 'AllocationRules'[Sender]
var hoursspent = CALCULATE(SUM('Fact'[Spenthours]) , 'Fact'[Date] = __date , 'Fact'[Workcenter] = __sender)
var allparts =
CALCULATE(
CALCULATE(
SUM('AllocationRules'[Part])
, 'AllocationRules'[Sender] = __sender
)
, ALL(AllocationRules)
)
return
('AllocationRules'[Part] / allparts) * hoursspent
)
, BLANK()
)
)
``````

And a table visual:

Please be aware that changing allocation rules over time are not considered, but basically it will work, then it's necessary to identify the allocation rule that is valid at a given point in time.

Hopefully, this helps to create a solution.

Regards,
Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Solution Sage

## Re: spliting specific values based on the values of two columns and add to other rows

DAX doesn't really do individual row computations, so you will need to build yourself a table that has the instructions hard coded into it, and then use that in the DAX calculations.

I'm thinking something like:

 Source Destination Amount 100.DFT 100.D102 1/3 100.DFT 100.D103 2/3 100.DPT 100.D102 1 100.DTC 100.D102 1/3 100.DTC 100.D103 1/3 100.DTC 100.D202 1/3

Then you'll need to do a few DAX filters, comparisons and calculations to make it work, but should be able to get there.

If you can't articulate what you need in a table like the above, then you'll struggle to do this in Power BI.

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Microsoft

## Re: spliting specific values based on the values of two columns and add to other rows

Hi @pfz ,

You can follow the below steps to achieve it:

1. Create a rule table "Splits"

2. Create a calculated column with below formula:

``````Column =
var _DFT=CALCULATE(MAX('Splits'[DFT]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DPT=CALCULATE(MAX('Splits'[DPT]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DTC=CALCULATE(MAX('Splits'[DTC]),FILTER('Splits','Splits'[Workcenter]=EARLIER('Spent hours'[Workcenter])))
var _DFTSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DFT"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
var _DPTSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DPT"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
var _DTCSH=CALCULATE(MAX('Spent hours'[SpentHours]),FILTER('Spent hours','Spent hours'[Workcenter]="100.DTC"&&'Spent hours'[Date]=EARLIER('Spent hours'[Date])))
return IF('Spent hours'[Workcenter] in {"100.DFT","100.DPT","100.DTC"},BLANK(),'Spent hours'[SpentHours]+_DFT*_DFTSH+_DPT*_DPTSH+_DTC*_DTCSH)``````

Best Regards

Rena

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors