Aggregate by distinct date and then average

[Anonymous]

Hi, I am PowerBi newbie.

I want the Average Households (HH) by year in the below dataset. The issue is I have multiple household entries for the same date. I want PowerBi to aggregate by dinstinct date and and then give me an average of Households in a year. 

Date        HH          Area

1/1/17    1000        New York

1/1/17     2000       Washington DC

2/1/17     5000       Colorado

4/1/17     6000       Austin

5/1/18     5623       Washington DC

5/1/18     9875       Boston

6/1/18     9856       Austin

7/1/18     6541       New York

8/1/19     1236       San Jose

8/1/19     1026       Chicago

9/1/19     7895       Denver

What I am looking for (to be eventually showed in a bar chart with a trend line)

Year    Average Households (sum of HH by date divided by the number of DISTINCT dates)

2017     4666.67

2018     10631.67

2019     5078.5

[v-yuta-msft · Accepted solution]

@Anonymous ,

You can create a measure using DAX below:

Average Households = CALCULATE(SUM('Table'[HH]) / DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table', 'Table'[Date].[Year]))

 

Community Support Team _ Jimmy Tao

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

[HotChilli]

Create a measure

Measure = DIVIDE(SUM(Table1[HH]), DISTINCTCOUNT(Table1[Date]))