topic Re: Normalize Numeric Data in Quick Measures Gallery

Normalize Numeric Data

deldersveld — Sun, 30 Jul 2017 18:26:03 GMT

Normalize numeric data from several independent ranges to a uniform scale ranging from 0 to 1. This can be used as a precursor for clustering, creating parallel coordinates plots, and more.

Follows formula:

(x-min(x))/(max(x)-min(x))

Using the DAX measure:

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

eyJrIjoiNGM5NTJjMmQtZjZlOS00YjZjLWIwOGItZTlkNTJiMDM2YzNlIiwidCI6ImFjYzhhYWE1LWYxOTEtNDgyZi05MjFiLWNmNmMzM2E1ODgzMiIsImMiOjF9

Re: Normalize Numeric Data

Anonymous — Tue, 13 Nov 2018 19:59:49 GMT

How did you create the image of all the lines for the cars that were not selected in the background?

Re: Normalize Numeric Data

deldersveld — Tue, 13 Nov 2018 20:12:59 GMT

There are two line charts overlaid one on the other. The background one has consistent color for all lines, and the visual interactions are set so that car slicer will only filter the top line chart and not the background one.

I wrote a bit about the technique deep into this blog post: https://dataveld.com/2017/05/06/throwing-a-curve-at-the-power-bi-line-chart/

"If you would like to add other visuals to the report, crossfiltering will be an issue since the line chart does not allow highlighting. In this case, an overlay can be beneficial. This technique works for any line chart by the way, not just this curved one. By disabling the visual interaction for the “underlay” and setting a static Y Axis range for the overlay but keeping the default interaction set to filter, you can get a highlighting effect."

Re: Normalize Numeric Data

sweetstar — Tue, 20 Apr 2021 23:04:41 GMT

check this for excluding a filter:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#control-which-page-visuals-are-affected-by-slicers

Re: Normalize Numeric Data

SKan — Wed, 09 Aug 2023 19:31:21 GMT

Hi, Thanks for the article.

I have one question. Why did you use MAX(Table[Value]) for each data point? Wouldnt it return the max value for the whole "value" column, while w need the current cell value here.

VAR DetailValue = MAX('Table'[Value])