Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

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

 

 

Need Help
Show and Tell
Tips and Tricks

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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)

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak ,

       

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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 ?