Average quarter data by month

[siwyan_1]

I would like to average the quarterly Data by monty (right table, on MonthId 201510, sale data 2161650/3=720550), and add this number to left table accordingly.

Final look should be something like:

MonthId          TotalSales

201510            1287000(566450+720550)

201511            1523640(803090+720550)

201512            1597530(876980+720550)

Help please! 

[siwyan_1 · Accepted solution]

sssssssssssssss

[waltheed · Accepted solution]

I think I got it.

This is the measure:

Avg by Q = 
	CALCULATE(
		SUM('Quarter'[USD Net Rev])/3,
		ALLEXCEPT('FiscalYMD', 'FiscalYMD'[Year Quarter]),
		VALUES(FiscalYMD[Year Quarter] )
	)

I also added two columns Year Month and Year Quarter to your date table:

Year Month = FiscalYMD[Year] & "-" & format(FiscalYMD[Month], "00")

Year Quarter = FiscalYMD[Year] & " Q" & FiscalYMD[Quarter]

Then it looks like this: 

Because I use VALUES of the quarters in the measure you can leave the quarters out of the table.

Here is the link to your file.

[waltheed]

Do you have a date dimension table?

That would make it a bit easier. Then you can refer to quarters and months in your measures. 

[waltheed]

This may be what you need:

Create a new Measure:

Avg by Q = calculate(sum(FactOnlineSales[SalesAmount])/3 , all('Date'[CalendarMonth]))

And then another measure to sum the new one and your existing monthly total:

Total = [Your Monthly Totals] + [Avg by Q]

Would that work?

[siwyan_1 · Accepted solution]

sssssssssssssss

[waltheed]

Hi siwyan_1,

Can you please mark my post as the solution?

(Using a ; or , depends on your regional settings in windows. )

Thanks, Edgar

[siwyan_1]

Hi Edgar,

This still does not solve my problem. 

This is what I put into the measure: Avg by Q = CALCULATE(sum('Retail_Quarter'[USD Net Rev])/3,ALL('CalendarMonthYear'[MonthBeginID]))

The averaged data (right table)  does not fill into all monthes on left table. e.g. right table Month 201507 average is 791,697; this number only showed up on left table for month 201507. The 201508 and 201509 do not have any number fill in. 

I've been hitting my head against the wall the whole morning and trying to get it solved. but still no solution. Help please. 

[waltheed]

The difference with my example is that I have a date dimension table, which links the months to quarters.

Do you have that as well? It's highly recommended. 

[siwyan_1]

Hi Edgar,

Yes, I have a date dimention table. And montly sale and quarter sale tables are refering to the date dimention table. But still the averaged quarterly data still only fill in one month of that quater instead of all monthes. 

Please advise.

[waltheed]

Can you share your pbix file?

[siwyan_1]

Hi,

I don't know how to upload a file here. This is the link to download the file: 

https://fere.me/yaup/jbiqwq/

thank you for your help.

[waltheed · Accepted solution]

I think I got it.

This is the measure:

Avg by Q = 
	CALCULATE(
		SUM('Quarter'[USD Net Rev])/3,
		ALLEXCEPT('FiscalYMD', 'FiscalYMD'[Year Quarter]),
		VALUES(FiscalYMD[Year Quarter] )
	)

I also added two columns Year Month and Year Quarter to your date table:

Year Month = FiscalYMD[Year] & "-" & format(FiscalYMD[Month], "00")

Year Quarter = FiscalYMD[Year] & " Q" & FiscalYMD[Quarter]

Then it looks like this: 

Because I use VALUES of the quarters in the measure you can leave the quarters out of the table.

Here is the link to your file.

[siwyan_1]

It works perfect without any additional dimentions. After adding in the 'platform' dimention, it cracked again.  

Any idea? Thank you so much for your help. 

[Anonymous]

Hello,

i exactly need this but not able to get it.. can you help me out ? share me the sample pbix file if possible

[waltheed · Accepted solution]

It works with me...

[siwyan_1]

Sorry, this actuall not solved my problem. 

this is what I tried:

Avg by Q = CALCULATE(sum('Retail_Quarter'[USD Net Rev])/3,ALL('CalendarMonthYear'[MonthBeginDate].[Date]))

I tried to change the ',' marked in red above to ';'  but it gives me error message saying ':' is not allowed.

Help Please!