Use Statistic functions for DateTime column

[pgolbi]

Hello all,

I have a table with date column. Something like "Call Received". Business requires report for "busiest date/time" - drill down from month -> week -> week day -> hour. I created 2 dimension tables Date and Time and linked them to my main table. It works fine but I can only show COUNT of records from "Call Received". I believe it is because I am using datetime column (Received) that I only can do count or distinct count.

What is the best way to allow users select any stats function like Average, StDev, etc? I can create measures for each of function but there should be more elegant way to implement this. 

Many thanks in advance.

[Anonymous]

If I was doing this for my business, i'd make use of a Bar Chart where the Axis is "Hour".  You could create a new column (in DAX or M) that stores the Hour component for each record.  Next all you need to do is make a graph with the Hour column being the axis and the count being the value.

For increased reability you could also convert the Hour Column to store the data in a manner that suits displaying on the Axis.

If a histogram doesn't suit, you could make use of the TOP N function.

[pgolbi]

I already done it this way. Time and Date are linked to main table and I use Hour or Date/WeekDay/Month from dimension tables. 

The issue is that it only works for Count and Distinct Count functions. I want to use other functions like Average, Median, etc.

[Anonymous · Accepted solution]

You can create a Measure that provides you the GEOMEAN and MEDIAN over the dataset based on the filter context.

What exactly the is the roadblock you are facing? Is it that you want the user to be able to select which function they see the outcome to?

If thats all you need, produce a measure for each statistic type.  Create a selection table that forms the basis of a pick list.  Give each item a numeric value and create a pointer measure that checks what filter context is on your selector using a switch statement.  This would look something like

Stats Result = SWITCH([Stat Selection],
    2, [Average],
    3, [Median],
    4, [Standard Deviation],    
    [Count])

[pgolbi]

Thank you, I will give it a go. I am fairly new to Power BI so still a long way!