Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 colum...
Need Help
  • v-sathmakuri's avatar
    v-sathmakuri
    1 year ago

    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's avatar
Ashish_Mathur
Super User
1 year ago

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.