## Grouping by an Aggregation in DAX

January 17, 2013 Leave a comment

Business users really like the option of comparing category members against the average for all. Let’s review how to accomplish this in DAX.

Suppose you what to find out which months had an above average sales performance on the data above (tables come from AdventureWorksDW). We must first create a new table in the model, to hold the categories we want. In PowerPivot, these values can imported as a linked table:

The resulting model is the following:

As sales data is per day, but we want to find out the monthly average, we must use an expression that computes this aggregate at the correct level of granularity:

With this, we can now use a measure leveraging a conditional DAX statement to categorize the monthly output as needed:

Notice here we also compute at the monthly grain by use of the SUMX function iterating over the list of unique month names. This is important in order to allow for proper aggregation at the subtotal level.

The data model allows now to create an interesting visualization, using color as an indicator, to emphasize those months that are below average (to graph the dotted line, I added one more value to the linked table to allow for display of the computed average value itself):

__Grouping on Quartiles__

Another interesting example of is grouping data by quartiles. In order to do this, I modified Colin Banfield’s quartile DAX expression to dynamically compute based on the “utility” linked table which has no relationships with other tables in the data model, and contains the needed multipliers to compute at each percentile value.

A final expression allocates the sales amount to the appropriate quartile column, and ensure the computations happens at the product level:

Here is the result:

Notice that subtotals are computed on quartiles appropriate for the current category in the filter context. Same applies to the grand total.

In Power View, I cross referenced the sales amount (on X axis) with two additional, quartile based metrics: count of sold products per category (Y axis) and % sold products over all available category products (bubble size).

You can see that the “Component” product category had lots of sold products (out the entire category) on any given quartile, however “Bikes” had more products with sales on the 4th quartile than it did on the second or third, and overall was the most profitable category.