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

     

     

fab_incher's avatar
fab_incher
Frequent Visitor
3 years ago

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's avatar
    DataInsights
    Super User
    3 years ago

    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