cancel
Showing results for
Did you mean:
Frequent Visitor

## Average issue

Hi!

I am sure this is an easy one but I don't get how to get this right.

Consider the visualisations and the data below. For Month 1, the totalt hours is 12 which equals 4 hrs per person. However the average is calculated as 3 since there are 2 rows for Jack, hence 12/4=3 I guess. For month 2 the average is calculated correctly as 15/3 as there are only one row per person and month. How can I get an average for the total time for month 1, eg. 4 hrs per person?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Average issue

Hi @Fredrik,

You need to make the calculation considering only distinct values something like this:

```Average hours =
DIVIDE ( SUM ( Table[Hours] ); DISTINCTCOUNT ( Table[Name] ) )```

Based on context it will give you the Hours divided by number of unique users.

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2
Highlighted
Super User

## Re: Average issue

Hi @Fredrik,

You need to make the calculation considering only distinct values something like this:

```Average hours =
DIVIDE ( SUM ( Table[Hours] ); DISTINCTCOUNT ( Table[Name] ) )```

Based on context it will give you the Hours divided by number of unique users.

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: Average issue

Hi MFelix!

Thanks, that works like a charm. I thought it would be easier though. This appears to me as to be a very common scenario.

To get the average to respect the filter selected for week I need to add

Average hours = DIVIDE( DIVIDE ( SUM ( Table1[Hrs]); DISTINCTCOUNT ( Table1[Name] ) ); COUNTROWS(FILTERS('Table1'[Month])))

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 27 members 949 guests
Recent signins: