Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating an average over multiple entries

Hello 

 The dummy of the data I have is shown below, I am trying to calculate the average project span which would be (20+10+20)/3. However, there are multiple entries for the same project, and when I try to use the normal average in the visuals, the number is incorrectly calculated. I have attempted to use the measure averagex(summarise... but this also does not work. 

 

In summary, how do I calculate the average over one column, when the value may be repeated multiple times in the column for different rows. 

I am still new to PowerBi so struggling to get to grips with everything. 

thanks

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You could refer to my sample for details. If this is not what you want, please correct me and inform me your expected output.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try like

    divide(sumx(values(Table[project]), max(Table[Project Span])),distinctCOUNT(Table[project]))

    or

    avergageX(summarize(Table[project],"_1", max(Table[Project Span])), divide(sum(_1]),count([_1])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello thanks for replying

      I have tried this and it doesn't quite work, I don't think the summation is based on the individual project span. I am trying to figure out how to sum all the project span per project. The distinct count part works but the sum part doesn't.

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        Anonymous 


        Please share an example and provide sample data that we can copy.

        ________________________

        Did I answer your question? Mark this post as a solution, this will help others!.

        Click on the Thumbs-Up icon on the right if you like this reply 🙂

        YouTube, LinkedIn

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Anonymous 

    Try like

    Average_M = 
AVERAGEX(
    SUMMARIZE(
    TABLE,
    TABLE[PROJECT],
    "AVG", AVERAGE(TABLE[PROJECT SPAN])
    ),
    [AVG]
)

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Value calculated is incorrect

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        Anonymous 

        What is your expected average based on the sample you provided?

         Please provide more clarity to your question.

        ________________________

        Did I answer your question? Mark this post as a solution, this will help others!.

        Click on the Thumbs-Up icon on the right if you like this reply 🙂

        YouTube, LinkedIn

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You could refer to my sample for details. If this is not what you want, please correct me and inform me your expected output.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.