Forum Discussion

fab_incher's avatar
fab_incher
Frequent Visitor
3 years ago
Solved

Building a histogram based on a measure

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

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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