Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |