DAX Formula for Avg/Max Measure

[Jorge Santos]

Hi, this might be a quite trivial question, but even after reading my entire book from Marco/Alberto F. I'm still struggling with the following.
Most of my tables look like this:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table" border=0 cellborder=0 cellpadding=0 >
<tr class="tableizer-firstrow" bgcolor=#8fbc8f><th>ProdCategory</th><th>User</th><th>Value</th></tr> <tr><td>A</td><td>John</td><td>428</td></tr> <tr><td>B</td><td>John</td><td>159</td></tr> <tr><td>C</td><td>Mary</td><td>284</td></tr> <tr><td>A</td><td>Mary</td><td>39</td></tr> <tr><td>B</td><td>Mary</td><td>28</td></tr> <tr><td>C</td><td>Mary</td><td>853</td></tr> <tr><td>A</td><td>James</td><td>486</td></tr> <tr><td>B</td><td>James</td><td>958</td></tr> <tr><td>C</td><td>Rick</td><td>827</td></tr> <tr><td>A</td><td>Patricia</td><td>346</td></tr> <tr><td>B</td><td>Patricia</td><td>651</td></tr> <tr><td>C</td><td>Patricia</td><td>734</td></tr> <tr><td>A</td><td>Julian</td><td>348</td></tr> <tr><td>B</td><td>Julian</td><td>316</td></tr></table>

Each user could have multiple value rows for different product categories. Now, I use a metric on my analysis that is the maximum value for the user (regardless of the ProdCategory). Let's call that metric MaxValue. What I need to get is the average MaxValue by ProdCategory. I am trying to do this as a Measure as i need to apply various filter context above this.

I'm sure this would be a very easy statement with 2 nested CALCULATE statement or something similar, but so far I have been doing it on SQL and just importing the results table.

Any guidance would be much appreciated. Thank you.

 

[buclao]

hey Jorge,

Just to be clear, using the example table for Mary...you need to calculate the average of
853 (C)
39 (A)
28 (B)

so the measure should return the following: 306.67

If that's the case, I'm trying to come up with an idea so you don't have to write a measure for all the product categories and then take the average of all those measures.
Also, before I try to come up with an idea, what version of PP are you using? If you are actually using PPv2.0 it would be pretty simple just using the AVERAGEX with the SUMMARIZE function.

The SUMMARIZE function will get you a table with just the MAX of each product category and the averagex will be doing the the average for all the rows within the filters context.

If you are using PPv1.0 then it's another story and would probably need the help of a POWERPIVOTpro.

Hope this helps.

 

[buclao]

The actual formula would look something like this in PPv2.0
=AVERAGEX(
SUMMARIZE(Table1,
Table1[ProdCategory],
Table1[User],
"MAX Value",
MAX(Table1[Value])),
[MAX Value])

 

[buclao]

and the following results:
<table border="0" cellpadding="0" cellspacing="0" width="176"><colgroup><col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:69pt;font-size:11.0pt; color:black;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:none;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20" width="92">Row Labels</td> <td style="width:63pt;font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none" width="84">Measure 1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">James</td> <td align="right">722</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">John</td> <td align="right">293.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">Julian</td> <td align="right">332</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">Mary</td> <td align="right">306.6666667</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">Patricia</td> <td align="right">577</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">Rick</td> <td align="right">827</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:none;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Grand Total</td> <td style="font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:none;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">474.8461538</td> </tr> </tbody></table>

 

[Jorge Santos]

Buclao, thank you very much for this. I was indeed using v1. I have upgraded to v2 and this works perfectly. This summarize is just like a group by thing in sql, which is exactly what I needed. Thank you again.

 

[buclao]

np Jorge.

Glad it worked,
Miguel