Forum Discussion

siva3012's avatar
siva3012
Icon for Helper II rankHelper II
9 years ago
Solved

Create new measure to get Average

Hi, I have a table which contains Date, CustomerName, Price, month, & weekday.   I have created measure using the DAX, Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1)) simila...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi siva3012,

     

    Create an extra measure like:

    Count month selected = DISTINCTCOUNT(Actual[Month])

    Modify the measure for Day 1 as below:

    Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/[Count month selected]

    Result.

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi siva3012,

 

Create an extra measure like:

Count month selected = DISTINCTCOUNT(Actual[Month])

Modify the measure for Day 1 as below:

Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/[Count month selected]

Result.

 

Best regards,
Yuliana Gu

  • siva3012's avatar
    siva3012
    Icon for Helper II rankHelper II
    9 years ago

    v-yulgu-msft : Thank you for the solution.

  • siva3012's avatar
    siva3012
    Icon for Helper II rankHelper II
    9 years ago

    v-yulgu-msft : Hi, There is an small eror in the solution. If we select 2 Months data Jan & Feb in slicer. Jan has 31 days and Feb has 28 days. The average is been calaculated wrongly for Jan 29, 30 & 31. 

     Do you have any idea over this ?

     

     

    • siva3012's avatar
      siva3012
      Icon for Helper II rankHelper II
      9 years ago

      I have solved this issue,

      Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/(CALCULATE([Count month selected],Actual[Day]=1))

  • siva3012's avatar
    siva3012
    Icon for Helper II rankHelper II
    9 years ago

    v-yulgu-msft : Is it possible to draw an average line for those data which is shown in the graph ? Like this