Forum Discussion

PremaBoddeda's avatar
PremaBoddeda
Frequent Visitor
3 years ago
Solved

To Calculate average including the dates where values are not present

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
 

 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    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)

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      PremaBoddeda
      Frequent Visitor
      3 years ago

      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