Forum Discussion
Dynamic Standard Deviation
Hi,
I have a data table with the following attributes:
| Country | End of Week | Total Sales |
| US | 1/1/2022 | 100 |
| Canada | 1/1/2022 | 150 |
| France | 1/1/2022 | 75 |
| US | 1/8/2022 | 40 |
| Canada | 1/8/2022 | 150 |
| France | 1/8/2022 | 60 |
| US | 1/8/2022 | 90 |
| Canada | 1/15/2022 | 80 |
| US | 1/15/2022 | 90 |
| France | 1/15/2022 | 100 |
I would like to be able to create a measure for standard deviation of the 'Total Sales' column that changes dynamically if I adjust the date range or country in a visual. How can I do this?
Thanks for your help and suggestions!!
Hi jackj ,
Please try below steps:
1. create a measure with below dax formula
Standard Deviation of the Total Sales = VAR cur_country = SELECTEDVALUE ( 'Table'[Country] ) VAR max_date = MAXX ( 'Table', [End of Week] ) VAR min_date = MINX ( 'Table', [End of Week] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Country] = cur_country && 'Table'[End of Week] >= min_date && 'Table'[End of Week] <= max_date ) RETURN CALCULATE ( STDEV.P ( 'Table'[Total Sales] ), tmp )2. add some visuals like below
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi jackj ,
Please try below steps:
1. create a measure with below dax formula
Standard Deviation of the Total Sales = VAR cur_country = SELECTEDVALUE ( 'Table'[Country] ) VAR max_date = MAXX ( 'Table', [End of Week] ) VAR min_date = MINX ( 'Table', [End of Week] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Country] = cur_country && 'Table'[End of Week] >= min_date && 'Table'[End of Week] <= max_date ) RETURN CALCULATE ( STDEV.P ( 'Table'[Total Sales] ), tmp )2. add some visuals like below
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.