Dynamic Standard Deviation

[jackj]

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!!

[Anonymous · Accepted solution]

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.