Forum Discussion

AZAnalyst's avatar
AZAnalyst
Regular Visitor
8 years ago
Solved

Best Approach to Two Dax Challenges?

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." Lo...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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.