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

SamLester's avatar
SamLester
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

The .74 you are seeing is the calculation of 1 - DIVIDE([Hour Spent],[Total available hours]) (1 - 180.5/690.97) for the totals, not the average of the efficiencies measures. My suggestion would be to remove the Totals line and calculate a new measure that is the average of the efficiencies as opposed to applying the efficiencies formula to the totals, which is what you are currently doing.

 

Thanks,
Sam Lester (MSFT)

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