Calculate average instead Total in Matrix subtotals

[Anonymous]

Hello,

The matrix in the screenshot below compares two dates based on the date selection from the left filters.

I want to re-create the measure “Labour_Hour_Compare” to calculate the average instead total for the subtotals at the bottom of the Matrix but can’t figure out a way and need some help.

I have created a separate table to opull the dates to use  in the Labour_Hour_Compare” measure

Dates = VALUES('Sample Data'[Date])

I have included a sample from the dataset too.

Data Set :

Date                                        DayName     HourDesc    Location      Area          Labour_Hours

19/02/2021	Fri	0-1	A	R	0
19/02/2021	Fri	0-1	B	R	0
19/02/2021	Fri	0-1	C	R	1
21/02/2021	Sun	0-1	A	R	0
21/02/2021	Sun	0-1	B	R	0
19/02/2021	Fri	1-2	C	R	0
20/02/2021	Sat	0-1	B	R	0
20/02/2021	Sat	0-1	A	R	0
19/02/2021	Fri	2-3	C	R	0
19/02/2021	Fri	3-4	C	R	0
19/02/2021	Fri	10-11	A	R	0
19/02/2021	Fri	10-11	B	R	1
21/02/2021	Sun	10-11	B	R	0
20/02/2021	Sat	10-11	B	R	1
19/02/2021	Fri	4-5	C	R	0
20/02/2021	Sat	10-11	A	R	1
19/02/2021	Fri	5-6	C	R	0
21/02/2021	Sun	10-11	A	R	1
19/02/2021	Fri	6-7	C	R	0
19/02/2021	Fri	11-12	A	R	0
19/02/2021	Fri	11-12	B	R	0
21/02/2021	Sun	11-12	B	R	1
20/02/2021	Sat	11-12	A	R	1
19/02/2021	Fri	7-8	C	R	0
20/02/2021	Sat	11-12	B	R	0
19/02/2021	Fri	8-9	C	R	0
21/02/2021	Sun	11-12	A	R	1
19/02/2021	Fri	1-2	A	R	0
19/02/2021	Fri	10-11	C	R	0
19/02/2021	Fri	1-2	B	R	0
20/02/2021	Sat	1-2	A	R	0
19/02/2021	Fri	11-12	C	R	1
20/02/2021	Sat	1-2	B	R	0
21/02/2021	Sun	1-2	B	R	0
19/02/2021	Fri	12-13	C	R	0
21/02/2021	Sun	1-2	A	R	0
19/02/2021	Fri	12-13	A	R	0
19/02/2021	Fri	12-13	B	R	0
19/02/2021	Fri	13-14	C	R	1
21/02/2021	Sun	12-13	A	R	0
19/02/2021	Fri	14-15	C	R	1
20/02/2021	Sat	12-13	B	R	0
20/02/2021	Sat	12-13	A	R	1
21/02/2021	Sun	12-13	B	R	0
19/02/2021	Fri	15-16	C	R	0
19/02/2021	Fri	13-14	B	R	0
19/02/2021	Fri	13-14	A	R	1
19/02/2021	Fri	16-17	C	R	1
20/02/2021	Sat	13-14	B	R	0
20/02/2021	Sat	13-14	A	R	0
19/02/2021	Fri	17-18	C	R	1
21/02/2021	Sun	13-14	B	R	0
21/02/2021	Sun	13-14	A	R	1
19/02/2021	Fri	18-19	C	R	1
19/02/2021	Fri	14-15	B	R	1
19/02/2021	Fri	14-15	A	R	1
19/02/2021	Fri	19-20	C	R	0
20/02/2021	Sat	14-15	B	R	0
19/02/2021	Fri	20-21	C	R	1
21/02/2021	Sun	14-15	B	R	0
20/02/2021	Sat	14-15	A	R	0
21/02/2021	Sun	14-15	A	R	0

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Try this measure

Labour_Hour_Compare = 
VAR selected_Date =
    SELECTEDVALUE ( 'Dates'[Date], MAX ( 'Dates'[Date] ) )
return AVERAGEX(KEEPFILTERS(VALUES('Sample Date'[HourDesc])),CALCULATE ( SUM ( 'Sample Date'[Labour_Hours] ), 'Sample Date'[Date] = selected_Date )
)

Reference: https://community.powerbi.com/t5/Desktop/Avg-Subtotal/td-p/636622

Best Regards,

Stephen Tao

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