Skip to main content
cancel
Showing results for 
Search instead 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

Reply
pfz
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

DateWorkcenterSpentHours
01/01/2020100.DTC30
01/01/2020100.d102100
01/01/2020100.D103150
01/01/2020100.D202200
01/01/2020100.DFT20
01/01/2020100.DPT10

 

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
v-yiruan-msft
Community Support
Community Support

Hi @pfz ,

You can follow the below steps to achieve it:

1. Create a rule table "Splits"

rule table.JPG

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)

splits.JPG

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.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @pfz ,

You can follow the below steps to achieve it:

1. Create a rule table "Splits"

rule table.JPG

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)

splits.JPG

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.
AllisonKennedy
Super User
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:

 

SourceDestinationAmount
100.DFT100.D1021/3
100.DFT100.D1032/3
100.DPT100.D1021
100.DTC100.D1021/3
100.DTC100.D1031/3
100.DTC100.D2021/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.

 

 


Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

TomMartens
Super User
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.
image.png
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:
image.png
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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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