Forum Discussion
Help With Seasonality
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 |
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.
4 Replies
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]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.
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.
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.
