Help With Seasonality

[Anonymous]

Hi,
I am trying to calculate the total per financial account, average per financial account, variance by department.  The data looks like the first 4 columns and I want to calculate the last 3 columns by account (Sales, Payroll, etc, etc).  I can't get the filtering to work on my average x. Can anyone please help?  Here is how it might look in Excel with the first 4 being the data, the last 3 the results.   Thanks!

Dept	Month	Sales	payroll	Average for Period Sales	Average for Year	Variance for whole period
1	1/1/2023	500	200	557.5	527.5	-27.5
1	2/1/2023	505	201	557.5	527.5	-22.5
1	3/1/2023	510	202	557.5	527.5	-17.5
1	4/1/2023	515	203	557.5	527.5	-12.5
1	5/1/2023	520	204	557.5	527.5	-7.5
1	6/1/2023	525	205	557.5	527.5	-2.5
1	7/1/2023	530	206	557.5	527.5	2.5
1	8/1/2023	535	207	557.5	527.5	7.5
1	9/1/2023	540	208	557.5	527.5	12.5
1	10/1/2023	545	209	557.5	527.5	17.5
1	11/1/2023	550	210	557.5	527.5	22.5
1	12/1/2023	555	211	557.5	527.5	27.5
2	1/1/2023	560	212	557.5	587.5	-27.5
2	2/1/2023	565	213	557.5	587.5	-22.5
2	3/1/2023	570	214	557.5	587.5	-17.5
2	4/1/2023	575	215	557.5	587.5	-12.5
2	5/1/2023	580	216	557.5	587.5	-7.5
2	6/1/2023	585	217	557.5	587.5	-2.5
2	7/1/2023	590	218	557.5	587.5	2.5
2	8/1/2023	595	219	557.5	587.5	7.5
2	9/1/2023	600	220	557.5	587.5	12.5
2	10/1/2023	605	221	557.5	587.5	17.5
2	11/1/2023	610	222	557.5	587.5	22.5
2	12/1/2023	615	223	557.5	587.5	27.5

[v-sathmakuri]

Hi @Anonymous ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sathmakuri · Accepted solution]

Hi @Anonymous ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Mathur  and @parry2k  for prompt repsonse.

Below are the columns which i have created to meet the requirements. I have attached the pbix for reference.

Average for Period Sales = AVERAGE('Sheet'[Sales])

Average for Year Sales =

CALCULATE(

    AVERAGE('Sheet'[Sales]),

    ALLEXCEPT('Sheet', 'Sheet'[Dept])

)

Variance Sales =

'Sheet'[Sales] - [Average for Year Sales]

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks & Regards,

Rekha,

CustomerSupportTeam.

[Ashish_Mathur]

Hi,

Try these calculated column formulas

Averge for period sales = CALCULATE(AVERAGE(Data[Sales]),ALL(Data))

Averge for year = CALCULATE(AVERAGE(Data[Sales]),FILTER(Data,Data[Dept]=EARLIER(Data[Dept])))

Variance for whole period = Data[sales]-Data[Averge for year]

Hope this helps.

[parry2k]

@Anonymous try these measures:

Avg for Period Sales = CALCULATE ( AVERAGE ( 'Table'[Sales] ), ALLSELECTED () )

Avg for Year = CALCULATE ( AVERAGE ( 'Table'[Sales] ), ALLSELECTED (), VALUES ( 'Table'[Dept] ) )

Variance = SUM ( 'Table'[Sales] )  - [Avg for Year]