Distribution Chart Power BI with Gradient Color Using Dax

This is simply the combination of Matrix and Scatter plot in Power BI.

Problem

To create this scatter plot, need Retention Rate on X-axis and Region on Y-axis, however, scatter plot doesn't let you place qualitative (Non numerical) data on its axis.

Solution

To overcome this, create a column which assign number values to each Region Name. If a column has many unique values, may create an index column in Power Query.

Region Number =
SWITCH(
? ? 'State Region'[Region],
? ? "Central",1,
? ? "East",2,
? ? "South",3,
? ? "West",4
)         

Now place this on Y-axis(any aggregration would work here)

Region Separator Lines

May turn off the titles, remove y-axis labels etc to make it look cleaner.

To generate lines separating regions, simply go analytics pane and add y-axis constant lines for the values of 0.5, 1.5, 2.5, 3.5 and 4.5.

Color

For the color code, wrote a DAX measure, and placed it in marker color.

Retention Rate | Color Code =


VAR MaxRating = 
? ? MAXX(
? ? ? ? Allselected('State Yearly Data'[State]),
? ? ? ? [Retention Rate]
? ? )
VAR MinRating = 
? ? MINX(
? ? ? ? Allselected('State Yearly Data'[State]),
? ? ? ? [Retention Rate]
? ? )
?
VAR MaxPct = 0.85
VAR MinPct = 0.56
VAR _colorHitTarget = "hsl(0, 0%, 90%)"


VAR _colorDefinition =
? VAR _hue = "20"
? VAR _saturation = "75"
? VAR _range = MaxPct - MinPct
? VAR _normalizedNumber = MinPct + ((_range * ([Retention Rate] - MinRating)) / (MaxRating - MinRating))
? VAR _lightness = FORMAT ( _normalizedNumber, "#%" )
? RETURN
? ? ? "hsl(" & _hue & "," & _saturation & "%," & _lightness & ")"


VAR Result = 
? ? IF(
? ? ? ? [Retention Rate] >= [Retention Rate | Target], 
? ? ? ? _colorHitTarget, 
? ? ? ? _colorDefinition
? ? )




Return 
? ? Result         

Let's break it down.

I used hsl color code, since we can control the brightness of the hsl color using percentage [ hsl(hue ,"saturation%," lightness%") ]. Keeping hue and saturation% constant, lightness% is altered based on the values of Retention Ratio. However I dont want it to be too dark or too light, so set a thrushold of 0.85 and 0.56 (i.e. MaxPct & MinPct)

MaxRating and MinRating will provide us the maximum and minimum values of Retention Ratio. Then used a mathematical formula to normalize those Retention Ratio values with Min value as 0.56 and Max value as 0.85

In the end, if the value is lower than the target, it will give that gradient color ( _colorDefinition ). If it is higher or equal to the target, it will give grey ( _colorHitTarget )

Combining both visual, have to tweak the height and padding in both the visual and turn on the grid lines on matrix to make them look like a single visual.