Forum Discussion

Burmeind's avatar
Burmeind
New Member
3 years ago
Solved

Measuring weighted dwell time

Below is my current table setup. I'm trying to measure average weighted dwell across these tables. Each order on Shipment Detail counts as one SID, and then there are entries on Multiple SID when an order contains more than one SID. The formula should be taking the date difference between ship_date and rcvdate multiplied by the number of SIDs for each order's weighted dwell and then dividing that by total number of SIDs. I just can't figure out how to do this in Power BI.

 

dwell

numsids

 wgt
105 50
24 8
81 8
11 1
55 25
44 16
= 30= 20 =108

Not correctly counting the numSIDs gets you 30/6 = 5, correctly counting numSIDs but not weighing them gets you 30/20 = 1.5, and correctly weighing them and counting them gets you 108/ 20 = 5.4 which should be the correct average dwell.

 

I thought maybe creating a new column on Fact Shipment Detail to hold the ship_date - rcvdate value per row, and then another new column holding the count of SIDs however numSids = DISTINCTCOUNT('Fact Multiple SID'[MOSD_Index]) is returning in every row the total count of entries on Fact Multiple SID (400,000) rather than the number of entries per row, which is under 5. 

 
I also tried creating a measure numsids2 = sum(count(related('Fact Multiple SID'[MOSD_Index])) + 1) but that just gives me an error.
 
Any help is appreciated and please let me know if i didn't articulate the issue well enough.

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    You can achieve this through SUMX.  Pseudo code:

     

     = DIVIDE(SUMX(table, dwell * numsids),SUMX(table,numsids),0)

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You can achieve this through SUMX.  Pseudo code:

     

     = DIVIDE(SUMX(table, dwell * numsids),SUMX(table,numsids),0)

  • Burmeind's avatar
    Burmeind
    New Member
    3 years ago

    Thanks for the answer. This is the final code I ended up with:

     

    Dwell time as column in Fact Shipment Detail

    Dwelltime = DATEDIFF('Fact Shipment Detail'[RcvDate],RELATED('Fact Manifest'[Ship_Date]),WEEK) * 5 + WEEKDAY(RELATED('Fact Manifest'[Ship_Date])) - WEEKDAY('Fact Shipment Detail'[RcvDate])

    Weighted Dwell as measure

    Weighted Dwell = 
    DIVIDE(
        SUMX('Fact Shipment Detail', 
            'Fact Shipment Detail'[Dwelltime] * 
            (counta('Fact Shipment Detail'[SID#]) + counta('Fact Multiple SID'[M_SID])
            )
            
            ),
        SUMX('Fact Shipment Detail',
            counta('Fact Shipment Detail'[SID#]) + 
            counta('Fact Multiple SID'[M_SID])),
        0
        )