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

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

aktripathi2506,

 

You can try to use AVERAGEX(TableName, [Measure]) as jahida mentioned. Another method is to change your Efficiency from measure to column, so that you can use the Quick Calc function (Average) in the Table chart.

If problem still persists, please provide some sample data of your tables so that we can solve it exactly.

 

Best Regards,

Herbert

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

    Thank you v-haibl-msftand jahida

     

    Please find below the example of raw data:

     

    weekhours spentAvailable hours for the week
    12200
    14200
    16200
    15200
    16200
    13200
    16200
    18200
    19200
    14200
    22400
    28400
    21400
    25400
    20400
    23400
    37500
    34500
    39500
    33500
    31500
    45100
    43100
    41100
    47100
    53600
    59600
    52600
    56600
    54600

     

    This is the raw data.

    The desired output is as below calculated in excel:

     

         
    WeekSum of Hoursaverage of available hoursefficiency% eff
    1532000.26574%
    2194000.047595%
    3245000.04895%
    4161000.1684%
    5246000.0496%
         
      Final exp. Result0.1189%
         

     

     

    Now I am trying to do the same thing in power BI with the very large data set for 52 weeks.

    Please advise.

     

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

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      10 years ago

      hi aktripathi2506

       

      This is the result that you want?

       

      Efficiency per Week and Total

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

        Hi Vvelarde,

         

        Seems good. Can you please advise how did you do that so I can also try the same and then confirm you if it works for me too.

         

        Thanks.