Month: August 2020

Accessibility, Data Visualization, Microsoft Technologies, Power BI

Fun with Power BI and Color Math

Meagan Longoria

I recently published my color contrast report in the Power BI Data Stories Gallery. It allows you to enter two hex color values and then see the color contrast ratio and get advice on how the two colors should be used together in an accessible manner.

Screenshot of the Color Contrast calculator Power BI report. The report headline reads "How shoudl I use these colors together in my Power BI report?". There are 2 slicers that allow you to select colors by hex value. A contrast ratio is shown along with advice generated on how to use the colors.
Color contrast calculations in a Power BI report

I could go on for paragraphs about making sure your report designs are accessible and useful for your intended audience. But this post focuses on how I made this report.

The Calculations

Color contrast (as calculated in the WCAG 2.1 success criteria) is dependent on luminance. Luminance is the relative brightness of any point in a color space, normalized to 0 for darkest black and 1 for lightest white. In order to calculate color contrast you must first get the luminance of each color.

As an example, I have colors #F3F2F1 and #007E97. In this hex notation, often explained as #RRGGBB, the first two digits represent red, the second two digits are green, and the last two digits are blue. Each two digits is a value that represents the decimal numbers 0 to 255 in hexadecimal notation. The same red, green, and blue values can be represented in decimal notation as integers, and this is what is used to calculate luminance. #F3F2F1 is RGB(243, 242, 241), and #007E97 is RGB(0,126,151).

On a side note, there are places in Power BI where we can change the transparency of the color which is referred to as RGBA (where A represents opacity/transparency). But whenever you copy a hex color value out of the color palette in Power BI, you will just see the 6 digits without the A because the A is stored separately in the UI. When you set colors using DAX formulas, you can specify the A value.

The sRGB color space is non-linear. It compensates for humans’ non-linear perception of light and color. If images are not gamma-encoded, they assign too many bits or too much bandwidth to highlights that humans can’t distinguish, and too few bits to shadows to which humans are sensitive and would require more bits to maintain the same visual quality. To calculate luminance we have to linearize the color values.

For each color component (R,G,and B), we first divide our integer value by 255 to get a decimal value between 0 and 1. Then we apply the linearization formula:

  • if R sRGB <= 0.04045 then R = R sRGB /12.92 else R = ((R sRGB +0.055)/1.055) ^ 2.4
  • if G sRGB <= 0.04045 then G = G sRGB /12.92 else G = ((G sRGB +0.055)/1.055) ^ 2.4
  • if B sRGB <= 0.04045 then B = B sRGB /12.92 else B = ((B sRGB +0.055)/1.055) ^ 2.4

Note: You will find sources online that that incorrectly use the number 0.03928 in the linearization formula instead of .04045. My understanding is that this is incorrect for sRGB.

Then we plug those values in to calculate luminance:

L = 0.2126 * R + 0.7152 * G + 0.0722 * B

The luminance of #F3F2F1 is .8891. The luminance of #007E97 is .1716.

The final calculation is color contrast:

(L1 + 0.05) / (L2 + 0.05), where

  • L1 is the relative luminance of the lighter of the foreground or background colors, and
  • L2 is the relative luminance of the darker of the foreground or background colors.

The color contrast between #F3F2F1 and #007E97 is 4.24, and we usually write this as 4.24:1. You can check my math here.

The Dataset

The source data for the report is generated entirely in Power Query. It starts with a simple list of the integers 0 through 255. I placed this in a query called Values.

let
    Source = List.Numbers(0,256),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}})
in
    #"Changed Type"

My linearization function is called ColorConvert.

(colornum as number) =>
let 
    Source = if colornum < .04045 then colornum/12.92 else  Number.Power(((colornum+0.055)/1.055),2.4)
in
    Source

My main query is called color 1. This is where all the calculations through luminance are done.

let
    //Get values 0 - 255
    Source = Values,
    //Call that column R for Red
    #"R Dec" = Table.RenameColumns(Source,{{"Column1", "R Dec"}}),
    //Crossjoin to Values to get Green values 0 - 255
    #"G Dec" = Table.AddColumn(#"R Dec", "Custom", each Values),
    #"Expanded G Dec" = Table.ExpandTableColumn(#"G Dec", "Custom", {"Column1"}, {"G Dec"}),
    //Crossjoin to Values to get Blue values 0 - 255
    #"B Dec" = Table.AddColumn(#"Expanded G Dec", "B", each Values),
    #"Expanded B Dec" = Table.ExpandTableColumn(#"B Dec", "B", {"Column1"}, {"B Dec"}),
    //Get hexidecimal values for R,G,B
    #"R Hex" = Table.AddColumn(#"Expanded B Dec", "R Hex", each Text.End("00" & Number.ToText([R Dec], "x"),2)),
    #"G Hex" = Table.AddColumn(#"R Hex", "G Hex", each Text.End("00" & Number.ToText([G Dec], "x"),2)),
    #"B Hex" = Table.AddColumn(#"G Hex", "B Hex", each Text.End("00" & Number.ToText([B Dec], "x"),2)),
    //Concatenate to get full 6-digit Hex color value
    #"Changed Hex Type" = Table.TransformColumnTypes(#"B Hex",{{"R Hex", type text}, {"G Hex", type text}, {"B Hex", type text}}),
    #"Full Hex" = Table.AddColumn(#"Changed Hex Type", "Hex", each [R Hex] & [G Hex] & [B Hex]),
    //Convert integers to decimals and linearize
    #"R Lin" = Table.AddColumn(#"Full Hex", "R Lin", each ColorConvert(([R Dec]/255))),
    #"G Lin" = Table.AddColumn(#"R Lin", "G Lin", each ColorConvert(([G Dec]/255))),
    #"B Lin" = Table.AddColumn(#"G Lin", "B Lin", each ColorConvert(([B Dec]/255))),
    //Calculate luminance with the linearized values
    #"Luminance" = Table.AddColumn(#"B Lin", "Luminance", each 0.2126 * [R Lin] + 0.7152 * [G Lin] + 0.0722 * [B Lin]),
    #"Changed Luminance Type" = Table.TransformColumnTypes(#"Luminance",{{"Luminance", type number}}),
    //Create a column for hexidecimal value with the hash/pound at the beginning
    #"Hex Dup" = Table.DuplicateColumn(#"Changed Luminance Type", "Hex", "Hex With Hash"),
    #"Hex with Hash" = Table.TransformColumns(#"Hex Dup", {{"Hex With Hash", each "#" & _, type text}}),
    //Remove Hex and linearized RGB columns to keep model under 1 GB limit for Pro license
    #"Removed Columns" = Table.RemoveColumns(#"Hex with Hash",{"R Hex", "G Hex", "B Hex", "R Lin", "G Lin", "B Lin", "Hex"}),
    //Rename Hex with Hash to Hex
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Hex With Hash", "Hex"}})
in
    #"Renamed Columns"

In order to allow users to choose two colors, I made a reference query to Color 1 called Color 2. 

let
    Source = #"Color 1"
in
    Source

If you are interested in these Power Query scripts, you can get them from this Gist.

DAX Calculations

The color contrast calculation is a DAX measure because it is dynamically calculated based upon the colors selected in the report. 

Color Contrast = 
If( Max('Color 1'[Luminance]) > MAX('Color 2'[Luminance]),
    Divide((Max('Color 1'[Luminance]) + 0.05) , (Max('Color 2'[Luminance]) + 0.05)),
    Divide((Max('Color 2'[Luminance]) + 0.05) , (Max('Color 1'[Luminance]) + 0.05))
)

The advice given based upon the color contrast ratio is also a DAX measure. 

Advice =
IF (
    [Color Contrast] < 3,
    "Not enough contrast for text or non-text content, use only for decorative items",
    IF (
        [Color Contrast] < 4.5,
        "Appropriate for large text at least 18pt, bold text at least 14 pt, or non-text content",
        IF (
            'Color 1'[Color Contrast] >= 4.5,
            "Appropriate for any size text and any non-text content"
        )
    )
)

The example charts showing the two colors as foreground and background are SVG measures. 

Chart 1 =
VAR Bkgrnd =
    MAX ( 'Color 1'[Hex] )
VAR Frgrnd =
    MAX ( 'Color 2'[Hex] )
VAR chart = "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='100' height='100' viewBox='0 0 24 24' style='background-color:" & Bkgrnd & "'><path fill= '" & Frgrnd & "' d='M7 19h-6v-11h6v11zm8-18h-6v18h6v-18zm8 11h-6v7h6v-7zm1 9h-24v2h24v-2z'/></svg>"
RETURN
    chart

Chart 2 =
VAR Bkgrnd =
    MAX ( 'Color 2'[Hex] )
VAR Frgrnd =
    MAX ( 'Color 1'[Hex] )
VAR chart = "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='100' height='100' viewBox='0 0 24 24' style='background-color:" & Bkgrnd & "'><path fill= '" & Frgrnd & "' d='M7 19h-6v-11h6v11zm8-18h-6v18h6v-18zm8 11h-6v7h6v-7zm1 9h-24v2h24v-2z'/></svg>"
RETURN
    chart

The check or x mark to indicate whether the colors can be used together in a graph or in text is created using Unicode characters. 

UseInGraph =
IF ( [Color Contrast] < 3, "✗", "✔" )

UseInText =
IF ( [Color Contrast] < 4.5, "✗", "✔" )

The RGB value shown for each color in the report is a DAX measure because storing it in the model made the model size larger than 1 GB, which would have prohibited me from deploying the report and publishing it to the web. 

RGB1 =
VAR R =
    SELECTEDVALUE ( 'Color 1'[R Dec] )
VAR G =
    SELECTEDVALUE ( 'Color 1'[G Dec] )
VAR B =
    SELECTEDVALUE ( 'Color 1'[B Dec] )
RETURN
    R & "," & G & "," & B

Check Out the Report

This post was an enjoyable combination of color, Power BI, and a bit of math. It was fun to make the report since it brought together my interests in accessibility and Power BI model optimization. At the least I’m hoping this gives you some exposure to how accessibility guidelines are applied to reports. If you are like me, you’ll find the color math fascinating and go down that rabbit hole.

Take a few seconds, pick some colors, and give the report a try.

Accessibility, Conferences, Microsoft Technologies, PASS Summit, Power BI

I’m Speaking at Virtual PASS Summit 2020

Meagan Longoria

PASS Summit has gone virtual this year, but that isn’t keeping PASS from delivering a good lineup of speakers and activities. I’m excited to be presenting a pre-con and two regular sessions this year. I know virtual delivery changes the interaction between audience and speaker, and I’m going to do everything I can to make my sessions more than just standard lecture and demo to keep things interesting.

Building Power BI Reports that Communicate Insights and Engage People (Pre-Con)

If you are into Power BI or data visualization, check out my pre-con session. It’s called Building Power BI Reports that Communicate Insights and Engage People. Unless we’ve had data visualization training, the way we learn to make reports is by copying reports that others have made. But that assumes other people were designing intentionally for human consumption. Another issue is that we often mimic example reports from tool vendors. That can be very helpful with the technical aspects of getting content on the page, but we often overlook the design aspects of reports that can make or break their usability and effectiveness in communicating information. My pre-con will begin with discussion on how humans interpret data visualizations and how you can use that to your advantage to make better, more consumable visualizations. We’ll take those lessons and apply them specifically to Power BI and then add on some tips and tricks. Throughout the day, there will be hands-on exercises and opportunities for group conversation. And you’ll receive some resources to take with you to help you continue to improve your report designs.

Agenda slide from my pre-con session: 1) Defining Success, 2) Message & Story, 3) Designing a Visual, 4) Refine Your Report 5) Applied Power BI 6) Power BI Tricks 7) Wrap-Up
Agenda for my PASS Summit pre-con titled Building Power BI Reports that Communicate Insights and Engage People

This session is geared toward people that have at least basic familiarity with Power BI Desktop (if you can populate a bar chart on a report page, that’s good enough). If you have never opened Power BI Desktop, we might move a little fast, but you are welcome to join us and give it a try. If you are pretty good with Power BI Desktop, but you want to improve your data visualization skills, this session could also be a good fit for you. I hope you’ll register and join my pre-con.

Implementing Data-Driven Storytelling Techniques in Power BI

Data storytelling is a popular concept, but the techniques to implement storytelling in Power BI can be a bit elusive, especially when you have data values that change as the data is refreshed. In this session, we’ll talk about what is meant by story. Then I’ll introduce you to tool-agnostic techniques for data storytelling and show you how you can use them in Power BI. We’ll also discuss the visual hierarchy within a page and how that affects your story. You can view my session description here.

Inclusive Presentation Design

I’m also delivering a professional development session for those of us that give presentations. Most speakers have good intentions and are excited to share their knowledge and perspective, but we often exclude audience members with our presentation design. Join me in this session to discuss how to design your presentation materials with appropriate content formatted to maximize learning for your whole audience. You’ll gain a better understanding of how to enhance your delivery to make an impact on those with varying abilities to see, hear, and understand your presentation. You can view my presentation description here.

Other Pre-Cons from My Brilliant Co-Workers

If you aren’t into report design, my DCAC coworkers are delivering pre-cons that may interest you.

Denny Cherry is doing a pre-con session on Microsoft Azure Platform Infrastructure.

John Morehouse is talking about Avoiding the Storms When Migrating to Azure.

I hope you’ll join one of us for a pre-con as well as our regular sessions. With PASS Summit being virtual, the lower price and removal of travel requirements may make this conference more accessible to some who haven’t been able to attend in past years. Be sure to get yourself registered and spread the word to colleagues.