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.
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:
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.
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.
As an Amazon Associate I earn from qualifying purchases.