Dynamic filtering based on selected value in slicer in Power BI

Question

I am trying to create a calculated table where the data is being taken from another table and calculating the average based on the username, total average and variance between the 2 of these columns.

To create a table, I used the below DAX in Power BI which calculated the average based on the username.

scanner_speed_average_calculation = 
    SUMMARIZE(scanner_speed
        ,scanner_speed[user_name]
        ,"Average"
        ,AVERAGE(scanner_speed[order_processed]))

To calculate the group_average I used the below DAX:

group_average = 
    SUMMARIZE(
        scanner_speed
        , "Group Avg"
        , average(scanner_speed[order_processed]))

And finally to calculate the variance, I used this query:

Variance = scanner_speed_average_calculation[Average] - scanner_speed_average_calculation[group_average]

Below is an outcome of these calculations.

I want to be able to make these calculations dynamic based on the selected value from the date. The table where I am taking these calculations do have the date value. I want to be able to use date range in slicer and I want these values to change based on the selected date range. I tried few things with Filter, Selectedvalue but I am not sure if I used them correctly.

Below is a main table where I took all these calculations from.

Below is a visual of where I want to group_average and variance. I want to be able to use date range and these columns should change accordingly.

Any idea or help will be appreciated. If possible then please put the entire formula. I am still a newbie in the world of DAX. Thanks in advance

power bi file

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2021-12-30T19:16:53

If you want a calculation to depend on a slicer, you need a Measure, not a calculated column or calculated table. Calculated columns and calculated tables are generated on refresh and physically stored in your model, so the slicers can filter them, but the slicers can't change the value of the calculations.

Measures are not persisted, but are calculated as needed based on changes to filters and slicers.

If you simply add add a measure

AverageOrdersProcessed := AVERAGE(scanner_speed[order_processed])

and put that on a visual that groups by user_name, you will get a the AVERAGE(scanner_speed[order_processed]) for each `user_name'.

Dynamic filtering based on selected value in slicer in Power BI

1 Answers