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 ...
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)

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
        )