Best Approach to Two Dax Challenges?

[AZAnalyst]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Let's solve the first one.  Try this

=(CALCULATE(SUM(ConsumptionData2017[Gal (000s)]),ConsumptionData2017[Rate Class]="Residential")/CALCULATE(DISTINCTCOUNT(ConsumptionData2017[Location ID]),ConsumptionData2017[Rate Class]="Residential"))/12

Does this work?

[Anonymous]

This should solve the first part of your problem:

Avg Monthly Vol Residential :=
CALCULATE (
    DIVIDE (
        SUM ( ConsumptionData2017[Gal (000s)] ),
        DISTINCTCOUNT ( ConsumptionData2017[Location ID] )
    ),
    ConsumptionData2017[Rate Class] = "Residential"
)

Notice that you're first modifying the filter context to [Rate Class] = "Residential",  then you're adding up all of the [Gal 000s] and dividing by the distinct count of locations.

Also, be careful when using FILTER().  In each of the instances you added it, it's not needed (and will likely slow down your queries).  FILTER() is only needed when you're trying to compare a column to a calculated amount (usually a measure).  If you're trying to compare a column to a fixed value, just add that statement directly to CALCULATE() as I have shown above.

The reason is FILTER() is an iterative function, and you're telling DAX to iterate over all 1.2 million rows to search to see if they should be included.

Now, onto the 2nd part:

I don't think you need a disconnected slicer table.  You can build 3 separate measures and add them to a table visual along with Month as you've shown.

I think something like this would work:

# Customers 1-5% Over Average =
VAR AverageUsage = [Avg Monthly Vol Residential]
RETURN
    SUMX (
        VALUES ( ConsumptionData2017[Location ID] ),
        IF (
            AND (
                CALCULATE ( AVERAGE ( ConsumptionData2017[Gal (000s)] ) )
                    >= AverageUsage * 1.01,
                CALCULATE ( AVERAGE ( ConsumptionData2017[Gal (000s)] ) )
                    <= AverageUsage * 1.05
            ),
            1,
            0
        )
    )

Essentially, this is iterating over a table of the unique Location IDs.  With each Location ID, it's calculating the average for that customer.  If that average is between 1-5% over the total average (using the measure name that I defined earlier), then give that row a 1 (otherwise a 0).  Then just add up all of the 1s and 0s to get the number of customers.You can repeat this same template and just replace the 1.01 and 1.05 with the other values you need.

If this measure behaves a little slow (as it very well could, since we're calculating an average twice for each of the 17k customers), you can try this.  Please note that I don't have DAX Studio open at the moment, so I'm unable to test if this syntax is correct (I normally don't declare variables in the middle of code, but that's required in this case).

This MIGHT work, but you may throw an error:

# Customers 1-5% Over Average =
VAR AverageUsage = [Avg Monthly Vol Residential]
RETURN
    SUMX (
        VALUES ( ConsumptionData2017[Location ID] ),
        IF (
            VAR IndividualAverage =
                CALCULATE ( AVERAGE ( ConsumptionData2017[Gal (000s)] ) )
            RETURN
                AND (
                    IndividualAverage
                        >= AverageUsage * 1.01,
                    IndividualAverage
                        <= AverageUsage * 1.05
                ),
            1,
            0
        )
    )

Let us know if these solve the issue for you!

[AZAnalyst]

Hi Chris,

I tried implementing the DAX formula for the second problem, and it returns only zeros for every band I created. My intial  filter context is a pivot table with Months on the rows.

I am using DAX in PowerPivot so the only change I made was "=" to ":=". What is the function of the pound sign at the beginning of your first statement? Thanks!

[AZAnalyst]

Hi Chris,

Thank you. Your answer to question #1 worked fine. Thanks for the 411 on the Filter function. I removed it from all of my Calculate formulas. I appreciate this because I still must add six more years of historical data to the database. 

Please see my response to Ashish reading the calculation output as a total of averages rather than a calulated monthly average for the year on the grand total line of a pivot table. If I use the Average function, it calculates the monthly average for the year on the grand total.

Why doesn't this statement work:?

Test Dax:=CALCULATE (AVERAGE ( ConsumptionData2017[Gal (000s)] ),DISTINCTCOUNT ( ConsumptionData2017[Location ID] ),ConsumptionData2017[Rate Class] = "Residential")

It throws a semantic error. "True/False expession does not specify a column. Each True/False expression used a a table filter expression must refer to exactly one column." 

Thanks!

[Anonymous · Accepted solution]

Looks like you're missing a DIVIDE() statement in the first parameter of your CALCULATE() ...

Test Dax :=
CALCULATE (
    DIVIDE (
        AVERAGE ( ConsumptionData2017[Gal (000s)] ),
        DISTINCTCOUNT ( ConsumptionData2017[Location ID] )
    ),
    ConsumptionData2017[Rate Class] = "Residential"
)

PS...go to www.daxformatter.com to get the nicely formatted code that I've been providing.  It's a HUGE help, especially with more complex DAX.

Regarding wanting to see the monthly average in the grand total, you may want to use the following pattern:

IF(

  HASONEVALUE( Month ),

  [Subtotal measure for 1 month],

  [Different measure for calculating in the grand total]

) 

There's some good documentation on this already.  Check PowerPivotPro's website.

[AZAnalyst]

Thanks, I'll use daxformatter. As to the formula, it generates an answer of 0.00043775. I have no idea what that number represents.

[AZAnalyst]

It tried this as well to define the filter as a table expression... to no avail:

Test Dax:=CALCULATE (AVERAGE ( ConsumptionData2017[Gal (000s)]),DISTINCTCOUNT( ConsumptionData2017[Location ID] ),FILTER(ALL(ConsumptionData2017,ConsumptionData2017[Rate Class]= "Residential")))

[Ashish_Mathur · Accepted solution]

Hi,

Let's solve the first one.  Try this

=(CALCULATE(SUM(ConsumptionData2017[Gal (000s)]),ConsumptionData2017[Rate Class]="Residential")/CALCULATE(DISTINCTCOUNT(ConsumptionData2017[Location ID]),ConsumptionData2017[Rate Class]="Residential"))/12

Does this work?

[AZAnalyst]

Hi Ashish,

Thank you, yes it works. I achieved the same answer by using two measures I previously created: 

Avg Vol Per Residential Loc:=DIVIDE([Total Residential Vol],[Residential Locations])

The answer is the same as your approach. I didn't need to divide by 12 because I'm dropping this measure onto a pivot table with an intitial filter context of Calendar[Date(Month)]. 

What I don't get with your approach or mine is the monthly average on the grand total line. Instead, it calculates the annual average, which is a correct number, it's just that i would like to see the monthly average. My intitial calculation does generate what I'm looking for but the value seems incorrect.

Taking a step back to the table. Any idea why the difference in averages? Distinctcount calculates 16,930 unique Location IDs.

If I filter the table manually by Rate Class = Residential which is 206,652 rows, then divide by 12 for each month per resident, I end up with 17,221. A difference of 291 records compared to the distinct count of residents. Weird. Any thoughts? Thanks!

[Ashish_Mathur]

Hi @AZAnalyst,

You are welcome.  Share the link from where i can download your PBI file.  Show the place where the answer is wrong and also let me know what the correct answer should be.

[Anonymous]

@Ashish_Mathur I forgot to divide by 12 as you did, good catch!

=
CALCULATE (
    DIVIDE (
        SUM ( ConsumptionData2017[Gal (000s)] ),
        DISTINCTCOUNT ( ConsumptionData2017[Location ID] )
    ),
    ConsumptionData2017[Rate Class] = "Residential"
)
    / 12

I think you can combine the arithmetic steps into 1 calculate statement to force the right filter context.  And using DIVIDE() will catch division by zero errors.