how to calculate the average per day, basd on one value

[Anonymous]

Hi all, I have a question about the following:

I have a table, in which MON turnover is mentioned on 04/01/2018 (117.000)

Now I want to add a column, in which the average MON turnover per day is automatically calculated.

Can anybody help me with the DAX formula for this?

Thanks!

John

Date                 Turnover MON  Average Turnover per day MON

01/01/2018      0                        29.250  

02/01/2018      0                        29.250

03/01/2018      0                        29.250 

04/01/2018      117.000             29.250

[ChandeepChhabra]

@Anonymous,

Try this calculated column

=SUM('Table'[Turnover MON])/COUNTROWS('Table')

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(MAX(Data[Turnover MON])/COUNTROWS(Data),ALL(Data))

Hope this helps.

[Anonymous]

Good day Ashish,

Thanks for your reply, it helps me a lot!

However, now I face a new challenge. 

If I want to use this formula over a certain period, can I do that as well?

for example: I want to use this formula for the january data and seperately or for february data

Thanks upfront for your help in this,

John

Date                 Turnover MON  Average Turnover per day MON

01/01/2018      0                        29.250  

02/01/2018      0                        29.250

03/01/2018      0                        29.250 

04/01/2018      117.000             29.250

03/02/2018      0                        70.000

04/02/2018      210.000              70.000

05/02/2018      0                         70.000

[Ashish_Mathur]

Hi,

I created 2 calculated columns

Month-Year = FORMAT(Data[Date],"mmm-yy")

Result = CALCULATE(SUM(Data[Turnover Mon]),FILTER(Data,Data[Month-Year]=EARLIER(Data[Month-Year])))/CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Month-Year]=EARLIER(Data[Month-Year])))

Hope this helps.

[Anonymous · Accepted solution]

Hi Ashish,

I tried it and this is exactly what I need.

Thanks a lot!

John

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[danextian]

Hi @Anonymous,

As a measure, you can simply use

Avg Turnover MON - Measure =
AVERAGE ( Table[Turnover MON] )

and then place this measure in a table with dates. The measure will be automatically filtered based on the date.

As a calculated column, you need to use CALCULATE() function to specify a filter

Avg Turnover MON - Column =
CALCULATE (
    AVERAGE ( 'Table'[Turnover] ),
    /* This is the filter telling DAX to 
     "For the whole table, get me the average of Turnover MON 
     if the date is equal to this one." */
    ALL ( 'Table' ),
    'Table'[Date] = EARLIER ( 'Table'[Date] )
)

Alternatively, you can also use the fomula below which result is the same as the one above.

Avg Turnover MON - Column2 =
CALCULATE (
    AVERAGE ( 'Table'[Turnover] ),
    ALLEXCEPT ( 'Table', 'Table'[Date] )
)