cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Super User

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(
, 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
)
, 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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors