Forum Discussion
Calculated Column in a slicer - Using between - Max value = max sum of number column
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)
Hi Anonymous,
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
2 Replies
Hi Anonymous,
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