Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Aggregate by distinct date and then average

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

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create a measure using DAX below:

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

Capture.PNG 

 

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.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create a measure using DAX below:

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

Capture.PNG 

 

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
Super User
Super User

Create a measure

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.