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 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's avatar
Ashish_Mathur
Super User
8 years ago

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.