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 pull 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
21/02/2021	Sun	0-1	A	R	1
21/02/2021	Sun	0-1	B	R	1
21/02/2021	Sun	10-11	B	R	1
21/02/2021	Sun	10-11	A	R	1
21/02/2021	Sun	11-12	B	R	1
21/02/2021	Sun	11-12	A	R	1
21/02/2021	Sun	1-2	B	R	1
21/02/2021	Sun	1-2	A	R	1
21/02/2021	Sun	12-13	A	R	1
21/02/2021	Sun	12-13	B	R	1
21/02/2021	Sun	13-14	B	R	1
21/02/2021	Sun	13-14	A	R	1
21/02/2021	Sun	14-15	B	R	1
21/02/2021	Sun	14-15	A	R	1
21/02/2021	Sun	15-16	A	R	1
21/02/2021	Sun	15-16	B	R	1
21/02/2021	Sun	16-17	A	R	1
21/02/2021	Sun	16-17	B	R	1
21/02/2021	Sun	17-18	B	R	1
21/02/2021	Sun	17-18	A	R	1
21/02/2021	Sun	18-19	B	R	1
21/02/2021	Sun	18-19	A	R	1
21/02/2021	Sun	2-3	C	R	1
21/02/2021	Sun	1-2	C	R	1
21/02/2021	Sun	0-1	C	R	1
21/02/2021	Sun	11-12	C	R	1
21/02/2021	Sun	2-3	D	R	1
21/02/2021	Sun	1-2	D	R	1
21/02/2021	Sun	0-1	D	R	1
21/02/2021	Sun	22-23	D	R	1
21/02/2021	Sun	12-13	D	R	1
21/02/2021	Sun	10-11	D	R	1
21/02/2021	Sun	2-3	E	R	1
21/02/2021	Sun	1-2	E	R	1
21/02/2021	Sun	0-1	E	R	1
21/02/2021	Sun	22-23	E	R	1
21/02/2021	Sun	12-13	E	R	1
21/02/2021	Sun	11-12	E	R	1
21/02/2021	Sun	10-11	E	R	1
21/02/2021	Sun	2-3	F	R	1
21/02/2021	Sun	23-0	F	R	1
21/02/2021	Sun	8-9	F	R	1
21/02/2021	Sun	6-7	F	R	1
21/02/2021	Sun	0-1	G	R	1

[Anonymous]

I really need to figure this out and would appreciate if anyone knows or have come across any scenario as such ?

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

Labour_Hour_Compare = 
var selected_Date = 
  SELECTEDVALUE(
    Dates[Date],
    MAX(Dates[Date])
  )
var tab = 
  FILTER(
    'Sample Data',
    'Sample Data'[Date] = selected_Date
  )
return 
  AVERAGEX(
    SUMMARIZE(
      tab,
      'Sample Data'[HourDesc],
      "@Labour_Hour", SUM('Sample Data'[Labour_Hours])
    ),
    [@Labour_Hour]
  )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

hi @v-kkf-msft ,

I have tried your DAX measure but it is still not pulling what it supposed to. Is there a way to share the sample pbix file ?

[v-kkf-msft]

Hi @Anonymous ,

This is my PBIX file, you can see the difference.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EYleB8-QLmtIqasW9CLk-s4BXioY0vAQl09Cep_IxWAxbQ?e=xnVSNj

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft ,

That is not what I am after. 

What I really want is to compare the data using two time frames. I want to use two date slicers as per the screenshot and compare the data in the matrix  .

[amitchandak]

@Anonymous , Try like

Labour_Hour_Compare_Avg =
var selected_val=SELECTEDVALUE(Dates[Date],max(Dates[Date]))
Return AVERAGEX (
SUMMARIZE (
'Sample Data',
'Sample Data'[HourDesc],
"@Labour_Hour",CALCULATE( SUM ( 'Sample Data'[Labour_Hours] ), filter('Sample Data','Sample Data'[Date]=selected_val) )
),
[@Labour_Hour]
)

[Anonymous]

@amitchandak ,

I tried that and it is returning a blank column now .

[amitchandak]

@Anonymous , In the second formula, where you have used summarize. In the same way create the third one. Just use the calculation of third formula, in the measure created inside summarize. 

I have discussed this topic in video - https://www.youtube.com/watch?v=qE5KBp4uP0g

[Anonymous]

@amitchandak ,

I don't think that video covers what I am after.

I changed the measure as suggested but it not responding to the second date filter.

Labour_Hour_Compare_Avg =

var selected_val=SELECTEDVALUE(Dates[Date],max(Dates[Date]))

Var patrn_hrs = AVERAGEX (

SUMMARIZE (

        'Sample Data',

        'Sample Data'[HourDesc],

         "@Labour_Hour", SUM ( 'Sample Data'[Labour_Hours] )

      ),

    [@Labour_Hour]

   )

Return CALCULATE(patrn_hrs,'Sample Data'[Date]=selected_val)

[amitchandak]

@Anonymous , what is the expected value for labour_hour_avg and Labour_hour_compare at line and at Total level

[Anonymous]

@amitchandak ,

labour_hour_avg is working perfectly. I want the same behaviour to be applied to Labour_Hour_Compare based on the second date filter on the screenshot (as you can see it is summing at the total level instead average)