Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
@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.
Create a measure
Measure = DIVIDE(SUM(Table1[HH]), DISTINCTCOUNT(Table1[Date]))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |