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.

AZAnalyst's avatar
AZAnalyst
Regular Visitor
8 years ago

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!

AZAnalyst's avatar
AZAnalyst
Regular Visitor
8 years ago

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")))