Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate average instead Total in Matrix subtotals

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.

image.png

image.png

Data Set :

 

DateDayNameHourDescLocationAreaLabour_Hours
21/02/2021Sun0-1AR1
21/02/2021Sun0-1BR1
21/02/2021Sun10-11BR1
21/02/2021Sun10-11AR1
21/02/2021Sun11-12BR1
21/02/2021Sun11-12AR1
21/02/2021Sun1-2BR1
21/02/2021Sun1-2AR1
21/02/2021Sun12-13AR1
21/02/2021Sun12-13BR1
21/02/2021Sun13-14BR1
21/02/2021Sun13-14AR1
21/02/2021Sun14-15BR1
21/02/2021Sun14-15AR1
21/02/2021Sun15-16AR1
21/02/2021Sun15-16BR1
21/02/2021Sun16-17AR1
21/02/2021Sun16-17BR1
21/02/2021Sun17-18BR1
21/02/2021Sun17-18AR1
21/02/2021Sun18-19BR1
21/02/2021Sun18-19AR1
21/02/2021Sun2-3CR1
21/02/2021Sun1-2CR1
21/02/2021Sun0-1CR1
21/02/2021Sun11-12CR1
21/02/2021Sun2-3DR1
21/02/2021Sun1-2DR1
21/02/2021Sun0-1DR1
21/02/2021Sun22-23DR1
21/02/2021Sun12-13DR1
21/02/2021Sun10-11DR1
21/02/2021Sun2-3ER1
21/02/2021Sun1-2ER1
21/02/2021Sun0-1ER1
21/02/2021Sun22-23ER1
21/02/2021Sun12-13ER1
21/02/2021Sun11-12ER1
21/02/2021Sun10-11ER1
21/02/2021Sun2-3FR1
21/02/2021Sun23-0FR1
21/02/2021Sun8-9FR1
21/02/2021Sun6-7FR1
21/02/2021Sun0-1GR1

 

 

11 REPLIES 11
Anonymous
Not applicable

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

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]
  )

image.png

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
Not applicable

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 ?

image.png

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
Not applicable

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  .

image.png

 

amitchandak
Super User
Super User

@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]
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak ,

 

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

Deevs_411470_0-1614153350067.png

 

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@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
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@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)

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.