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

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

Have you tried either of the methods we suggested?

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

Yes I tried your its not giving me the expected result. I thought about creating the new column before as well but it is difficult because: you have to get the sum of hours grouping by week and then divide it by the average of available hours group by week.

 

In a new column i can not do  [hours spent/available hours for each week] for each row because that will give me different value.

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

    What do you have in PowerBI right now? Do you only have one table or have you built out that summary table at all yet? I think both of us assumed you had that summary table built out, which might be incorrect.

    • jahida's avatar
      jahida
      Icon for Impactful Individual rankImpactful Individual
      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

        Awesome. This is great.

        Very good logic, solved the problem.

        Thanks a lot truely.

    • 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?