Forum Discussion

aktripathi2506's avatar
aktripathi2506
Icon for Helper IV rankHelper IV
10 years ago
Solved

Calculating average of measures

I have created a measure which calculate the efficiency per week by following formula: 1 - DIVIDE([Hour Spent],[Total available hours])   I have calculated week after week this, now I want the ave...
  • jahida's avatar
    jahida
    10 years ago

    Asumming you don't have that summary table built out and you just want to plug the initial table into a matrix or table visual, here's a measure you can use (might be minor syntax errors, I'm not testing but I'm pretty sure the concept is sound):

     

    AVERAGEX(SUMMARIZE(Table, Table[week], "Sum of Hours", SUM(Table[hours spent]), "Average available", AVERAGE(Table[Available hours for the week])), 1 - DIVIDE([Sum of Hours], [Average available]))

aktripathi2506's avatar
aktripathi2506
Icon for Helper IV rankHelper IV
10 years ago

Hi,

Thank you for the reply. Yes .74 is becoz of  (1 - 180.5/690.97) . 

But its not possible to create a measure which is average of another measure, it should be column as average function only accepts a column reference as an argument. Thats why its not working in my case.

 

jahida's avatar
jahida
Icon for Impactful Individual rankImpactful Individual
10 years ago

I need a bit more information about the structure of your data to give an exact answer, but in general, the simple work-around to the AVERAGE function requiring a column is to use AVERAGEX(TableName, [Measure]). So something like AVERAGEX(Table, ___) might work, where ___ is either [Efficiency] or the DAX expression used to generate [Efficiency].