Calculated Column in a slicer - Using between - Max value = max sum of number column

[Qotsa]

Hi,

I have a calc column that sums the duration.

The data is filtered by week number in a slicer

When I apply my calc col to a slicer and set to between, the min value = 30 and max value = 60. This make sense as the smallest value of duration = 30 and max = 60.

I want the max value to be = max sum of duration for an empid based on the selected week number and the min value to be = the min sum of duration for an empid based on the selected week number.

In my sample data, if week =1 is slected in slicer then I want to see my duration slicer to show a min value = 120 (duration = 30 * 4 for emp id = 1) and max malue = 300 (duration = 60 * 5 for empid = 2)

[Qotsa]

TY very much.

[Anonymous · Accepted solution]

Hi @Qotsa,

You can create a variable to calculate the average based on current week group, then you can use it as condition to filter records to get min and max summary values:

minValue =
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[duration] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[weeknum] )
    )
RETURN
    SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [duration] < _avg ), [duration] )

maxValue =
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[duration] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[weeknum] )
    )
RETURN
    SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [duration] > _avg ), [duration] )

Regards,

Xiaoxin Sheng