Search code examples
exceldaxpowerpivot

Calculating median of differences of columns

I am tracking production of a lot of items. For each item I have timestamps along about 15 registration points in the production process. I want to create a Pivot with statistics of the time it takes items to go from point x to point y.

A simplified dataset:

simplified dataset

Say I want to analyze how much time it takes from packing to shipping. I'm trying to create a measure like this:

PackToShip:=CALCULATE(
  MEDIAN(
      Shipped-Packed
  );
  Shipped<>BLANK();
  Packed<>BLANK()
)

So far I only get error messages that "The MEDIAN function only accepts a column reference as the argument number 1."

I know I could create calculated columns for each combination of points, but for 15 points that's a lot of columns. Can I do a measure "conditional (calculate) Median of a difference of two datetime columns" without creating a column for differences?

Solution

  • Create a measure:

    PackToShip =
MEDIANX (
    FILTER (
        'Dataset',
        'Dataset'[Shipped] <> BLANK () &&
        'Dataset'[Packed]  <> BLANK ()
    ),
    DATEDIFF ( 'Dataset'[Packed], 'Dataset'[Shipped], MINUTE )
)

    where 'Dataset' is the name of your table.

    The formula first filters your dataset to avoid blanks, then iterates the resulting table record by record, calculating difference between start and end dates in minutes (you can change that to another interval). Finally, it computes the median of the calculated differences.