Greetings,
I have a table of municipal water consumption records. I'm trying to create a monthly average of gallons used by residents. I need to filter the table by Rate Class = "Residential." Looks easy enough, but my DAX calcs seem wrong. Here's the DAX:
Avg Monthly Vol Residential:=CALCULATE(AVERAGE(ConsumptionData2017[Gal (000s)]),FILTER(ConsumptionData2017,ConsumptionData2017[Rate Class]="Residential"))
It's seems wrong because each Resident has twelve records, one for each month. The above formula is returning the average based on "all" the Residential rows which number 206,652. So, if we take Total Residential Vol which is 1,531,545,000 divided by 206,652 equals a Monthly Average of 7,411. (Monthly because all monthly rows are included.) However, if I take the Total Residential Volume of 1,532,545,000 and divide it by the number of unique Residential IDs which is 16,930 (produced by Residential Locations:=CALCULATE(DISTINCTCOUNT(ConsumptionData2017[Location ID]),FILTER(ConsumptionData2017, ConsumptionData2017[Rate Class]="Residential")), then divide that by 12 for a monthly average of 7,538.
It seems I need to combine the above two statements but I don't know how. Thoughts for a solution?
The second question is more complicated. I want to create a pivot table of residents who consume between 1-5%, 6-10%, 11-20%, etc above the above calculated average. My thoughts are that I should create a disconnected table with the bands I want, then write DAX to compare the average value for each customer to the values in the band. I simply want to count the number of residents that fall into each band and display the values on a pivot table like this as an example:
Month 1-5% 6-10% 11-20%
Jan 4,096 2,600 870
Help? Thank you.
