Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

FInding average amount

Hi,

 

Can someone help me with this: 

 

I have two columns: User ID and Bookings ID. The rows in these columns contains numeric values showing the respective user id and id booking, I want to find the average number of bookings per user id. 

 

How could I go about this in Power BI? I do not want to see the total amount of bookings per user id, but the average. 

When applying the AVERAGE aggregate in the visual, or creating a measure, the output is totally wrong, probably because all of the booking id are in the thousands and the average of all these numbers would be high. Also, this is not really the correct approach since I want to the see how many bookings a user makes on average. 

 

Can someone help?

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

    You can try to filter out the faulty ID after the calculation, or add the IF judgment condition or the Filter function filter table in the measure.
    something like :IF (user_id ="101", null, Avragex() )  or  Calculate( Averagex(), Filter('table', user_id <>"101") )

     

    Or please consdier sharing a simple sample file without any sesentive information for further discussion,  then I can try to look into it to come up with a more accurate measure.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try to create a measure like

     

    averageX(values(Table[User_id]), calculate(count(Table[Booking_id])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi and thank you!

       

      Is there any way writing that measure while excluding a certain user id? I do not want to count the value of "110" in my calculation (this is a faulty ID". How would I incorporate this?

       

      Thank you for your assistance!

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    You can try to filter out the faulty ID after the calculation, or add the IF judgment condition or the Filter function filter table in the measure.
    something like :IF (user_id ="101", null, Avragex() )  or  Calculate( Averagex(), Filter('table', user_id <>"101") )

     

    Or please consdier sharing a simple sample file without any sesentive information for further discussion,  then I can try to look into it to come up with a more accurate measure.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.