How do I find the average of a all values of a column which is summed by date?

[Nikhil_bb]

Apologies if the question isn't phrased correctly, I have a table which has session_date and visitor_count.

The visitor count is summed for each session date. I would like to find the average + stddev.p and average - stddev.p (which I will call UCL and LCL) of these summed values in a seperate column and populate the same value to each cell.

The reason for doing so is that I can plot the UCL and LCL values as a straight lines on the graph as show below in Excel.

However when I try to find the average it gives me the average by each row, as shown below.

Could someone help me on how to go about getting the UCL and LCL plotted on the graph? Thanks in advance! 

[v-yingjl · Accepted solution]

Hi @Nikhil_bb ,

Sorry for replying late. Based on your description, you can create these measures:

avg = 
VAR _sum =
    CALCULATE ( SUM ( 'Table'[vistor count] ), ALL ( 'Table' ) )
VAR _count =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
RETURN
    _sum / _count

std dev = CALCULATE(STDEV.P('Table'[vistor count]),ALL('Table'))

UCL = [avg] + [std dev]
LCL = [avg] - [std dev]

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[FarhanAhmed]

you can try by creating a measure 

_VisitorCount = SUM(Table[Visitor Count])

_Avg = AVERAGEX(all('Table'[Date]),[_VisitorCount])

_Std Dev = CALCULATE(STDEV.P('Table'[Visitor Count]),ALL('Table'[Date]))