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.

parry2k's avatar
parry2k
Super User
1 year ago

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]