To Calculate average including the dates where values are not present

[PremaBoddeda]

Hi!

 I want to view the Average as a line so I can understand the Average Payments made in that period. The average line should change based on the values visible on the x-axis (so Avg will change when drill up happens). If Payment does not occur on any day the value should be considered as Zero and not Null. For example: In a week, only for 5 days payment has been done then it is calculated as total/5 whereas I need total/7. `I have used clustered column chart in which we have an average line in the analytics pane. So please help me.

Thanks,

Prema

[PremaBoddeda]

Thankyou so much @amitchandak 

[amitchandak · Accepted solution]

@PremaBoddeda . Try like

sum(Table[Payment])/ (datediif(min(Table[Date]), max(Table[Date]), day) +1)

or

new measure = //joined date table
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return

sum(Table[Payment])/ (datediif(_min, _max, day) +1)

[PremaBoddeda]

Hi@amitchandak

It is working as expected when I placed that measure in table it is being total but while placing the measure in line y-axis in Dual Axis Visual I am unable to get the line in datelevel. So could you please suggest A solution for this. I am attaching the Screen shot for your reference