Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 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/2021Fri0-1AR0
19/02/2021Fri0-1BR0
19/02/2021Fri0-1CR1
21/02/2021Sun0-1AR0
21/02/2021Sun0-1BR0
19/02/2021Fri1-2CR0
20/02/2021Sat0-1BR0
20/02/2021Sat0-1AR0
19/02/2021Fri2-3CR0
19/02/2021Fri3-4CR0
19/02/2021Fri10-11AR0
19/02/2021Fri10-11BR1
21/02/2021Sun10-11BR0
20/02/2021Sat10-11BR1
19/02/2021Fri4-5CR0
20/02/2021Sat10-11AR1
19/02/2021Fri5-6CR0
21/02/2021Sun10-11AR1
19/02/2021Fri6-7CR0
19/02/2021Fri11-12AR0
19/02/2021Fri11-12BR0
21/02/2021Sun11-12BR1
20/02/2021Sat11-12AR1
19/02/2021Fri7-8CR0
20/02/2021Sat11-12BR0
19/02/2021Fri8-9CR0
21/02/2021Sun11-12AR1
19/02/2021Fri1-2AR0
19/02/2021Fri10-11CR0
19/02/2021Fri1-2BR0
20/02/2021Sat1-2AR0
19/02/2021Fri11-12CR1
20/02/2021Sat1-2BR0
21/02/2021Sun1-2BR0
19/02/2021Fri12-13CR0
21/02/2021Sun1-2AR0
19/02/2021Fri12-13AR0
19/02/2021Fri12-13BR0
19/02/2021Fri13-14CR1
21/02/2021Sun12-13AR0
19/02/2021Fri14-15CR1
20/02/2021Sat12-13BR0
20/02/2021Sat12-13AR1
21/02/2021Sun12-13BR0
19/02/2021Fri15-16CR0
19/02/2021Fri13-14BR0
19/02/2021Fri13-14AR1
19/02/2021Fri16-17CR1
20/02/2021Sat13-14BR0
20/02/2021Sat13-14AR0
19/02/2021Fri17-18CR1
21/02/2021Sun13-14BR0
21/02/2021Sun13-14AR1
19/02/2021Fri18-19CR1
19/02/2021Fri14-15BR1
19/02/2021Fri14-15AR1
19/02/2021Fri19-20CR0
20/02/2021Sat14-15BR0
19/02/2021Fri20-21CR1
21/02/2021Sun14-15BR0
20/02/2021Sat14-15AR0
21/02/2021Sun14-15AR0
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.