Calculate average by group which is unaffected by date slicer

[vazou19]

Hi there,

I am new here and I need your help.

Let's say we have the following dataset:

Date	Name	Shift	Measure
2/06/2022	Nick	morning	34
2/06/2022	George	morning	45
2/06/2022	Lisa	morning	23
2/06/2022	Mary	morning	14
3/06/2022	Nick	night	35
3/06/2022	George	night	24
3/06/2022	Lisa	night	34
3/06/2022	Mary	night	17

I would like a measure for conditional formatting purposes. So I want the all-time average of each type of shift. Then on my performance report, I want to show their stats in green color if they are above the average and red if they are below. In other words, if my report is for 3/06/2022, I want this measure to give me the all-time average of night shifts only and be unaffected by the selected date "3/06/2022". 

I hope it makes sense.

Thank you in advance.

[Anonymous]

Hi  @vazou19 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Date])

2. Create measure.

AVG =
var _select=SELECTEDVALUE('Table 2'[Date])
var _selectshift=
MAXX(FILTER(ALL('Table'),'Table'[Date]=_select),[Shift])
return
AVERAGEX(FILTER(ALL('Table'),'Table'[Date]<>_select&&'Table'[Shift]=_selectshift),[Measure])

color =
IF(
    MAX('Table'[Measure]) >=[AVG],"green","red")

3. Select [Measure] – Conditional formatting – Background color.

4. Format style – Field value – [color]

5. Result:

If you need pbix, please click here.

Calculate average by group which is unaffected by date slicer.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@vazou19 , a new measure

Calculate(AverageX(Summarize(Table,Table[Date], Table[Name], "_1",[Measure]) , [_1]), Filter(all(Table), Table[Shift] = max(Table[Shift]) ))

[vazou19]

Thank you for the prompt reply.

However, it doesn't let me write the [measure]. It gives me only the [date] and [name] options. Any thoughts?

Thanks