Forum Discussion

figo12's avatar
figo12
Icon for Helper I rankHelper 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 trie...
Need Help
GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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.

      • figo12's avatar
        figo12
        Icon for Helper I rankHelper I
        9 years ago

        Hi GilbertQ I already have the date value on date table (01/01/2017) related to the Sales Table Date, thing is I have like 20 data sheets that I would need to create a new month column and relate it to the primary date table, I believed there was an easier method in order to accomplish the goal. But thanks for your sugggestion anyway.