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

[pfz]

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

[v-yiruan-msft · Accepted solution]

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

[AllisonKennedy]

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.

[TomMartens]

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