Using DAX to Normalize Data in Power BI

A challenge to which I infrequently return is attempting to create various “new” chart types using only Power BI’s native visuals. For example, I have tried to replicate a Horizon chart and Slopegraph in Power BI without the use of custom visuals. In many cases, we can use DAX, selective filtering, or other tricks to obtain the “new” chart types.

Inspired by the variety of visuals in The Chartmaker Directory that Andy Kirk recently released, the most recent chart type that I have attempted to tackle is a Parallel Coordinates plot.

Parallel Coordinates

For this plot, several categories are available that do not adhere to a consistent numeric scale. For instance, using the automobile sample data, cylinders might vary from 4 to 8, while weight will be in thousands of pounds. In a standard Line Chart with the original data, you can see the impact:

BeforeNormalization.PNG

To obtain a uniform Y axis, I needed to normalize the measure values for all of the different X axis categories to a scale between 0 and 1.

The full creation of the Parallel Coordinates plot may be a good topic for a future post, but as a start, here is the DAX measure that made this chart possible. Each attribute has a variety of values (DetailValue) that fit along the scale between 0 (MinOfGroup) and 1 (MaxOfGroup). This provides uniformity so that whatever the natural range of the values, you end up with a scaled value that is consistent across all categories.

CalculateNormalizedValuesUsingDAX

Normalized Value = 
VAR MinOfGroup = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))
VAR MaxOfGroup = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))
VAR DetailValue = MAX('Table'[Value])
RETURN DIVIDE(DetailValue - MinOfGroup,MaxOfGroup - MinOfGroup,0)

 

If you already work with % of Total measures in DAX, think of normalization as a cousin. Instead of focusing on a SUM aggregation and assuming zero for a minimum, however, the DAX above establishes the range between MIN and MAX and determines at what percentage a value falls within that range. Depending on your data, the range can even involve MIN and MAX values below zero.

This measure formula as well as a PBIX file have been submitted to the Power BI Quick Measures Gallery.

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