Forum Discussion

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 trie...

Super User

9 years ago

You would need to create a New Measure

And then possibly put in the following code.

Average = DIVIDE(SUM('Tablename'[Total]),COUNTROWS('TableName'))

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
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.
- figo12
  Helper 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.
  - GilbertQ
    Super User
    9 years ago
    Hi figo12
    
    If I understand you should be able to consolidate all your sheets into one dataset, which will then mean that you now have got one Date field in your data, in which you can then map to your Date Table.
    
    In order to consolidate all the sheets, you would do this in the query editor, and possibly either use the Folder Source, or use the Append function to put them into one dataset?