Calculation of weight of month within quarter and average of every month weight

[Chava1881]

This might be difficult to explain, but I will do my best.

I got four fiscal years of sales data, each fiscal year has 4 quarters and each quarter has 3 months. My management wants to see a chart where I compare the same month in the same quarter in different years. that's the easy part, but they want to see the weight each individual month had in the quarter, so the weight can be also compared and an average of each month group. So we can see whether the forecast is off/on average and be able to spot outliers through time.

The graph I need to build looks like this:

the orange bar is the weight of each individual month within their quarter and there's a calculation for the average of the month thru the fiscal years. 

The data table looks like this (shorten version) 

Month	Sales	Quarter	Month #	Fiscal Year
Nov-17	143,984	Q1	1	2018
Dec-17	155,516	Q1	2	2018
Jan-18	123,475	Q1	3	2018
Nov-18	113,874	Q1	1	2019
Dec-18	118,754	Q1	2	2019
Jan-19	117,361	Q1	3	2019
Nov-19	107,632	Q1	1	2020
Dec-19	132,673	Q1	2	2020
Jan-20	118,782	Q1	3	2020
Nov-20	98,386	Q1	1	2021
Dec-20	121,322	Q1	2	2021
Jan-21	93,478	Q1	3	2021
Nov-21	116,455	Q1	1	2022
Dec-21	123,507	Q1	2	2022
Jan-22	95,660	Q1	3	2022

This is a mockup of how I got to calculate those numbers in the graph:

I have no idea how to create a measure that can give me the two things they are looking for, any suggestions or help would be greatly appreciated.

[sturlaws]

Hi, @Chava1881,

have a look at this mockup-report, I think it is almost what you are after:
sample report 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Chava1881]

Hello @sturlaws ,
it's almost perfect, and I really appreciate it! the only thing that is happening and I don't understand why the measure for the sales is not running the average for the monthgroup. It does show instead another calculation I couldn't decipher.

See screenshot below and link with PBIX

PBIX

I REALLY REALLY APPRECIATE YOUR HELP!!

[sturlaws · Accepted solution]

It's because the data in the report you provided has a finer granularity than the the mock-report I created. When the average of e.g. M10 is created, in your file the average is based on the rows instead of the sum of the month.

To achieve your desired outcome, change this part in [measure sales]

var _avg = CALCULATE(AVERAGE('Table'[Sales]),FILTER(ALL('Period'),Period[MonthGroup]=_mg))

to this 

var _avg = AVERAGEX(FILTER(ALL('Period'),Period[MonthGroup]=_mg),calculate(SUM(Total_Products_Database_Compute[Sales])))

[Chava1881]

I have no words to be able to express how grateful I am to you, people like you make this community amazing!
thank you very much!!