Grouping by an Aggregation in DAX

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

image

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:

image 

The resulting model is the following:

image

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:

image

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

image

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.

image

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

image

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.

image

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

image

Here is the result:

image

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.

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: