Monthly Average from Daily Date Values

[figo12]

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!

[GilbertQ]

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]

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]

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]

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]

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?

[figo12]

Hi @GilbertQ yes sorry I already have that,  what I am looking is just a measure that will accomplish my goal, I already have a date value related to all tables, and created a month id for each date, there must be a way to achieve this on a measure.

It is the same data sheet, single excel file, it is just like 20 pages on same excel file.

[GilbertQ]

Hi @figo12

If that is the case you can then simply use the measure I provided earlier?

And then on your Axis put in your Month and on the Values put in the Calculated Measure. That will then give you the average for each month.

[figo12]

Thanks @GilbertQ that would work fine, but what I am trying to implement is the KPI Gauge chart (sorry for been such a difficult case).

[GilbertQ]

hi @figo12

Ok so you are wanting to compare the daily value to the average?

And then based on that have another calculation which you can show on a KPI Guage?

And no problem, better to ask questions to find the result you are after.

[figo12]

Thanks @GilbertQ what I am trying to achieve is the following:

a. I would like to create a Monthly KPI Gauge Chart, Chart should change the monthly average amount when dashboard is filtered between different dates.

b.  The goal on such chart would be to show the accumulated sales amount vs the monthly average on a certain period of time (showed as an average per month), or if it is not possible, just to show the monthly average and on another chart I would show the accumulated sales amount for the dates period.

Thanks again!

[GilbertQ]

Hi @figo12,

You would then need to create 2 calculated measures, one for the Accumlated Sales and another for the Monthly Average.

For the Monthly Average calculation you would need to decide if you need it calculated for each individual month, or just the average over the date range selected.

In my opnion I would do the average based on what was selected. This is so that the range can be dynamic in nature, so that if a person selects 1 month, 2 months or even 2 weeks it will always show that as dynamic?