Building a histogram based on a measure

[fab_incher]

Hi folks,

i'm trying to build a histogram with my individual defined x-Axis, based on a measure.

The measure aggregates the elapsed time of different processing steps for finishing a single order. 

The problem is that my second measure for the Histogram-Count, counts every single step of the order and not the "distinct" Order_ID. In the provided example the histogram should only show 3 Columns (because there are 3 distinct Order_Ids for the filtered data), each with 1 count.

  --> 1 count for range 0-1 min (ID 3 with 0.7 min)

  --> 1 count for range 2-5 min (ID 5 with 4.5 min)

  --> 1 count for range 5-10 min (ID 1 with 8.7 min)

My datamodel has some other columns like category oder status, I want to be able to filter the histogram for that dimensions if possible.

Here is a screenshot of what i'm trying to do and how my input data looks like

Measure for time aggregation:

Average_Order_Time_Needed_Minutes = DIVIDE(SUM(Order_Processing[Time_Needed_Seconds])/60,DISTINCTCOUNT(Order_Processing[Order_ID]))

Measure for histogram count:

HistogramCount_Measure = 
COUNTROWS (
    FILTER (
        Order_Processing,
        [Average_Order_Time_Needed_Minutes] >= MIN ('Histogram_x-Axis'[Lower_Minutes] )
            && [Average_Order_Time_Needed_Minutes]< MAX ( 'Histogram_x-Axis'[Upper_Minutes] )
    )
)

Here is also the sample file, hope the link works: https://1drv.ms/u/s!AnU4nE8TJrdymVsVtodOk7nN8j3Y?e=y50exm

Many thanks!

Fabian

[fab_incher]

@DataInsights 

Many thanks for your solution, it works perfectly!

May I ask you an additional question?

How I can adjust the Measure to get

1. the Sum of minutes of each Range and
2. the Average of each Range?

I have the SUMX function in mind but I’m not able to adjust it…

Thank you very much!

[DataInsights]

@fab_incher,

Using the original pattern, you can add another column to the virtual table, and then sum it using SUMX:

HistogramSum_Measure = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Order_Processing[Order_ID] ),
        "@AvgOrdTime", [Average_Order_Time_Needed_Minutes],
        "@SumMinutes", CALCULATE ( SUM ( Order_Processing[Time_Needed_Seconds] ) )
    )
VAR vResult =
    SUMX (
        FILTER (
            vTable,
            [@AvgOrdTime] >= MIN ( 'Histogram_x-Axis'[Lower_Minutes] )
                && [@AvgOrdTime] < MAX ( 'Histogram_x-Axis'[Upper_Minutes] )
        ),
        [@SumMinutes]
    )
RETURN
    vResult

[DataInsights · Accepted solution]

@fab_incher,

Try this measure. The issue is that you were using FILTER which iterates the table, resulting in counting steps (rows) instead of Order ID. The VALUES function returns a table of distinct Order ID in the filter context, and ADDCOLUMNS evaluates [Average_Order_Time_Needed_Minutes] in the context of each Order ID.

HistogramCount_Measure = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Order_Processing[Order_ID] ),
        "@AvgOrdTime", [Average_Order_Time_Needed_Minutes]
    )
VAR vResult =
    COUNTROWS (
        FILTER (
            vTable,
            [@AvgOrdTime] >= MIN ( 'Histogram_x-Axis'[Lower_Minutes] )
                && [@AvgOrdTime] < MAX ( 'Histogram_x-Axis'[Upper_Minutes] )
        )
    )
RETURN
    vResult