Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average Standard deviation calculated per month

Hi I want to calculate a montly standard deviation 

I want to calculate the standard devation in DAX from my fact table but not the SD per row but totalled per month. 
My data looks like this 

CustomerdateSales
A1-1-202320
A15-1-202350
A5-2-202310
A10-3-2023150
B 1-1-202350
B5-3-2023

1

 

Now I want a DAx formula which first calculates the total sales per month. e.g. for customer A see below and after that calculates the standard deviation per month. 

 

CustomerMonthSales
A1-202370
A2-202310
A3-2023150

 

If i would calculate the st dev using 

STDEV.P(Tabel[Sales])  this gives me 57.35 which is correct. How can i build this logic in to a dax formula

hope you can help me

2 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous try measure 

    STDEVX.P(Sheet29,Sheet29[Sales])
    Sheet29 adjust to your table name
    Connect date column with Date table and check results. You provided sample of data so it is hard to check if it is ok. I hope this help
  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    if you want the sum of sales for each month alone, try using 

     

    SUMX(values([Month-Year]),[sales measure])

     

    or

     

    CALCULATE(SUM([Amount]),ALLEXCEPT(Table,[Month-Year],[Category]))

     


    this will give you sum of sales by month - year use the first if you dont have ctageories an dsub categories and use the second if you have categories and sub categories