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

In power BI I have the first table in my previous post refered as raw data.

 

That summary table I have given is just for understanding point of view (Excel way). 

 

I want to do all this calculation in power BI, in excel it is easy to do, thats why I gave example by excel that how to achieve the desired result.

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

Yeah ok my bad for misunderstanding. Did you try the most recent measure?

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

    Hi,

     

    What you mean by most recent measure? I tried the one you suggested.

    Thanks.

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

    Message 10 in the thread, I've pasted it here:

     

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