In this writing, I like to share how I started using the Dynamic Format String feature in Power BI, which was recently added as a preview feature.
Some days after mid of April 2023, many great Youtube videos and blog posts were created by people and I learned a lot from those.
I tried to focus on practicing and utilizing this feature to show less but valuable information on the more desired spots, and I focused on the Data labels option in a visualization.
In the visualizations, when developers turn on the Data labels option to show data points, the information is showing the data itself. However, if the data labels can show the information of comparison, not the information of the point itself, I think it will provide more valuable insights to users.
To move forward, my first idea was to create a line chart like the one below, complete with data labels.
The data labels above do not display the information for each data point. Instead, they indicate the difference ratio between the current year and the previous year to help users better understand the data. Furthermore, the decrements are highlighted in red to indicate negative values, while only the top-one increment is displayed in gray to show the month with the highest increase.
Below is how I did to create the above.
Step 1.
Turn on the preview feature, and create two measures and put them into the line chart.
Qty: = SUM( Sales[Qty] )
Qty previous year: = CALCULATE( [Qty:], SAMEPERIODLASTYEAR('Calendar'[Date] ))
Step 2.
Create one more measure like below, and put it into the same line chart.
Datalabel support: =
VAR _maxqty =
AVERAGEX(
ALLSELECTED ( 'Calendar'[Year Month], 'Calendar'[Year Month sort] ),
[Qty:]
)
RETURN
_maxqty * 0.2 //if developers want to show labels on the top area of the chart, try using 1.2 and MAXX, instead of 0.2 and AVERAGEX
Step 3.
Select the measure [Datalebel support:], and change the Format to "Dynamic".
Step 4.
Change "Measure" to "Format", and write the DAX code like below.
VAR _currentperiod = [Qty:] VAR _prevperiod = [Qty previous year:] VAR _ratio = DIVIDE ( _currentperiod - _prevperiod, _prevperiod ) VAR _maxratio = MAXX ( ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Year Month], 'Calendar'[Year Month sort] ), "@ratio", DIVIDE ( [Qty:] - [Qty previous year:], [Qty previous year:] ) ), [@ratio] ) VAR _minratio = MINX ( ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Year Month], 'Calendar'[Year Month sort] ), "@ratio", DIVIDE ( [Qty:] - [Qty previous year:], [Qty previous year:] ) ), [@ratio] ) VAR _result = SWITCH ( TRUE (), _ratio = _maxratio, FORMAT ( _ratio, "#0.0%" ), _ratio = _minratio, FORMAT ( _ratio, "#0.0%" ), _ratio < 0, FORMAT ( _ratio, "#0.0%" ), BLANK () ) RETURN """" & _result
Step 5.
Create a DAX measure like below.
Datalabel color = VAR _currentperiod = [Qty:] VAR _prevperiod = [Qty previous year:] VAR _ratio = DIVIDE ( _currentperiod - _prevperiod, _prevperiod ) VAR _maxratio = MAXX ( ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Year Month], 'Calendar'[Year Month sort] ), "@ratio", DIVIDE ( [Qty:] - [Qty previous year:], [Qty previous year:] ) ), [@ratio] ) VAR _minratio = MINX ( ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Year Month], 'Calendar'[Year Month sort] ), "@ratio", DIVIDE ( [Qty:] - [Qty previous year:], [Qty previous year:] ) ), [@ratio] ) VAR _result = SWITCH ( TRUE (), _ratio = _maxratio, "Gray", _ratio = _minratio, "Red", _ratio < 0, "Red", BLANK () ) RETURN _result
Step 6.
Change the measure name [Datalabel support:] to Diff.ratio, and format the line like below (Stroke width = 0, line color = white (the same as the background))
Only turn on the Data labels option for [Datalabel support:], and use the [Datalabel color] measure in the fx function for color.
Also, be aware that display units for the selected measure for the dynamic format string need to be described as None, like below.
Step 7.
The color red is only used to represent decrements, while the color gray is reserved for displaying the top increment.
Although there's always room for improvement in my approach, I hope the ideas presented here have inspired you to experiment with the dynamic format string feature in Power BI and find new ways to enhance your reports with this powerful tool.
Comments