Forum Discussion

figo12's avatar
figo12
Helper I
9 years ago

Monthly Average from Daily Date Values

Hi, I am having trouble getting the monthly average from individual  date values, data is structured as follows:

 

Date  /  Sales Total

01/01/2017 / 2000

01/02/2017 / 1000

etc

 

I already tried:

 

1) Created Calculated Column to show Month ID and created following formula as a measure:

 

SUM(Sales[Total]) / DISTINCTCOUNT(Date[Month No.])

 

But it is giving me the following error:

 

The SUM function only accepts a column reference as an argument.

 

I already tried to create a custom column instead of a calculated column but same error....

 

Any help would be greatly appreciated!

 

Thanks!

 

 

Need Help

11 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi figo12

     

    You would need to create a New Measure

     

    And then possibly put in the following code.

     

    Average = DIVIDE(SUM('Tablename'[Total]),COUNTROWS('TableName'))
    • figo12's avatar
      figo12
      Helper I
      9 years ago

      Hi GilbertQ Thanks for your help, just a question, with the proposed formula, Checking the formula it says count rows, so not sure if it would count the rows for each month, since I am looking for the monthly average, for example january, that would have number 1 as the id on the month table.

       

      Thanks again!

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi figo12,

         

        The COUNTROWS just counts the number of rows.


        So if you wanted to get the Average per month, you would either need to have a date table and create a relationship from your data's Date to the Date table.

         

        Or to use the built in time intelligence and use the Month.

         

        This will allow it to show the average per month.