Conditional Formatting to the MAX – Ep. 209

Conditional formatting is one of the fastest ways to add clarity to a report—and also one of the fastest ways to accidentally add noise.

In Ep. 209, Mike, Tommy, and Seth break down how they think about conditional formatting in Power BI: where it lives, what it’s actually communicating, and how to make the logic reusable so your formatting stays consistent as visuals (and requirements) change.

News & Announcements

• How to manage “Reporting Objects” in a Power BI Dataset — Practical guidance on keeping your dataset clean by separating core semantic objects from report-specific “helper” artifacts.

• OKVIZ — Card with States — A KPI-style card visual that can complement threshold-based formatting when you want a clear, standardized “state” indicator.

• ColorBrewer — A dependable reference for picking readable sequential/diverging palettes (including colorblind-safe choices) for gradient-style formatting.

Main Discussion

Conditional formatting is really three different tools that often get lumped together:

1. Color scales / gradients (relative position)
2. Rule-based thresholds (status against a target)
3. Icons (compact status/trend signals)

The episode’s through-line is simple: formatting should answer a question—not just decorate a number. If you can’t explain what the color or icon means in one sentence, it probably doesn’t belong.

Takeaways to apply right away:

• Anchor formatting on measures (not one-off visual settings) so the logic is reusable and easier to audit.
• Use gradients when you care about relative differences; use rules/icons when you care about thresholds (good/bad/on-track).
• Pick a single set of thresholds (and stick to it) so “green” means the same thing everywhere.
• Avoid relying on red/green alone—pair color with icons or labels for accessibility and clarity.
• Prefer fewer states; if you need five colors, it’s often a sign your KPI definition needs tightening.
• Name and document your formatting measures (e.g., Fmt Color - Variance) like any other shared semantic logic.
• When using icons, ensure they communicate the right concept (status vs. variance vs. trend) and don’t conflict with the chart’s own encoding.

Looking Forward

If you standardize formatting logic into measures and reuse a small set of thresholds, conditional formatting becomes a consistent UX pattern instead of a one-off visual trick.

Episode Transcript

0:29 good morning everyone welcome back to the explicit measures podcast with Tommy Seth and Mike getting good at that Mike getting good getting better at it finally I remember there was times where I was like welcome back to the measures explicit dang it let’s try it I’m gonna try nicknames now please you try to last time we got data mic Sensational Seth and tasty Tommy I don’t know [Laughter]

1:03 see it’s hard I’ve tried this it’s easy to get nicknames for you guys but it’s harder to get my own nickname because you’re like what do I want to call myself and you just get stuck you got to like think about it beforehand that’s why I think I can go with data mic because someone else made that one up it wasn’t me the only one that’s been relevant so far the only one who’s relevant to be Iron reporting yes that’s funny excellent excellent well jumping in today talking about some features in power bi desktop specifically the object and Reporting

1:35 specifically the object and Reporting and talking about conditional formatting maybe let’s let’s introduce conditional formatting why we use it where it is in power bi desktop and then maybe we can expand the idea from there where do you use it where do you put the measures for that how do you do the things around conditional formatting so let’s jump in there conditional formatting what does this mean this feels like an Excel type feature that we’ve every everything that I relate to is like does Excel do it oh it’s like Excel yeah okay I got it I

2:05 oh it’s like Excel yeah okay I got it I understand now I would I would argue the widest part of our audience probably already also understands Excel very well additionally I really do think actually in all seriousness that’s probably a good place to start in terms of the importance of conditional formatting most of us probably got introduced to it in Excel but that was basically a requirement if you did any reporting off of excel in the in your pivot tables to showcase that and if usually someone asked you for it or you added it yourself for reference

2:36 or you added it yourself for reference for me I know I added it a lot if especially about a large pivot table for my own reference to understand and it wasn’t even necessarily always for another audience but so that’s the introduction to where it’s relevant obviously potential formatting can be one of two ways or multiple ways but the two main Power hitters are your gradient which is showing low to high or high to low basically a color on a scale like from

3:06 basically a color on a scale like from red to green or based on rules where if it’s if it’s above this or below this and those are generally the ones that I think we’ve stuck into I you’d call them your your all-stars so to speak the All-Stars of conditional form we’re already going there what is your Mickey Mantle of conditional format good job sick is sticking with the sport for trying I tried to and I tried to stay with baseball because there’s many sport things I know but baseball and people in

3:36 things I know but baseball and people in sports is not what I I’m good at I I do know Cal Ripken Jr because he was and he’s only famous not for being an amazing player but for never skipping a game for like how many touchdowns did he score he said he was baseball good question yeah I guess who you’re doing there I see what you’re doing there it’s 3 700 700 some odd games or something like that that he had someone’s not to quote me on this one Mike I will buy you 10 Stakes you can name the player that he beat when he broke the record Mickey

4:07 when he broke the record Mickey Mantle no it was actually only two people Michael knows in baseball for those baseball nerds unfortunately Mickey middle is one of the most injured players for being one yeah I don’t know who he beat out do you yeah I don’t know who he beat out do who he beat out the Iron Horse Lou know who he beat out the Iron Horse Lou Gehrig oh there we go good call on that one I’m glad you knew it Tommy because I had no clue sorry

4:40 one other one other going back to conditional formatting after our nice baseball run so I would also add another item here around conditional formatting which would be icons oh so I agree with you Tommy I think coloring is very much a part of doing some conditional formatting but I think another part of this is using like a icons or threshold based conditional formatting like if it’s above this threshold give me a green checkbox or green check mark and then if it’s below the certain level give me like a red X

5:10 the certain level give me like a red X or or give me like a street light right it’s either red yellow green based on some metrics we have so there’s like some an if statement or if it’s the above this value do this color if it’s above this then do this and it’s above that then do the green or whatever so it’s a stacking effect and then using icons in your report as well so that would be one of the other ones I would put in there is as the All-Stars I think I see that one that one fairly frequently as well no as you should I think that’s that’s in all that that belongs in the

5:40 that’s in all that that belongs in the Hall of Fame yeah Green they should get the Lombardi trophy all this we’re saying is Excel so far or was introduced in Excel yeah I think so it’s was my first interaction with it Seth what do you think about conditional formatting pieces where were you first introduced to conditional formatting yeah definitely Excel right okay the most useful for me was just the the gradient like Tommy was talking about I think the even within power bi would Excel as well right the

6:10 power bi would Excel as well right the it’s a very easy way to implement visual indicators on on data right yeah and even if you’re sorting top to bottom there’s only like the threshold of like where do I really care about the the darkest red or when does it go into red to yellow or yellow to Green Etc and it we’re visual human beings like it’s it just added and adds the element of that grouping when we’re looking at

6:40 that grouping when we’re looking at tables of information which is typically where we’re using all this stuff stuff no I I know there’s so I’m gonna go a little bit further here there are other things inside is analysis Services where you actually can build a kpi in there as well is that is that a thing I think so I I haven’t played too much with it but I believe they have their own type like the K I know which drama the kpi is the Pacific it’s almost like calculation groups kpis yeah yes so it’s or it is

7:12 groups kpis yeah yes so it’s or it is basically yeah it is and and I and again I’m not quite sure I don’t use them and the other thing is I believe power bi can use them or consume them but you can’t make them inside a power bi data set it’s available in the structure of what power bi can do yeah it’s not necessarily be able to create it it can support it yes correct so then the reason I’m bringing that up is because if you look across like if you do a little research this is more of a legacy item I think the kpi metrics or kpi actually making a kpi yeah

7:44 actually making a kpi yeah is in a cube is part of this conditional formatting piece but that’s that’s more of data elements that are

7:50 that’s more of data elements that are part of the model so I guess we’re I guess maybe where my head’s going here a little bit is there’s a part of there’s two pieces we’re talking about there’s things that are model based and there’s things that are report based and we are this is what I talk when I talk I teach this in the classes is there’s like a dance between what does the model need to do and how does the model need to prep the data in order for the visuals to be able to be using the data elements to be able to configure something let me give you another

8:20 something let me give you another example of what by that if you want to have a spec a a single data in a column if you want a piece of information to always have a certain color color you can write a switch statement or a if statement that will return a hex value so in certain areas of power bi you can actually have the color returned as part of the measure so there’s conditional formatting based on okay here’s a bunch of data points okay should it be red yellow green or should we just return

8:51 yellow green or should we just return actually the hex code ddd12 or whatever the the color of that that value so you’re now building a data element inside the model that’s helping you generate some color or something inside the visual that it can read and I think that’s another part here that’s a bit tricky and confusing because because where should that measure live should that measure live in the thin report or should that measure be brought all the way back down to the data model and then

9:21 way back down to the data model and then how do you group them inside the data model so it doesn’t become confusing with all these very specific visual maybe even single visual specific measures that are are doing work just on a single visual I don’t know what are you guys thoughts on that if this is a if this is a dance or what’s your recommendation that that’s look like is it like a ballroom or no it would be definitely Tango it’s very passionate it’s a new year but it’s not it’s not like ding ding ding ding ding no no you wanna you wanna study like hey

9:52 no no you wanna you wanna study like hey this is a wealthy you spend a little time on this side of the room and then you work back in the model side I agree with that do I need to take the misses this type of dance yeah you’re taking the message to this dance like we haven’t tried to bring our mission yeah along for this ride this this Tango is quite spicy she fell asleep it’s definitely not the Tango yeah I think this is a conversation we’ve had about organization just to touch on where does some of those objects live to me if it is something that lives beyond

10:24 me if it is something that lives beyond that particular report when you’re building the model where it’s going to be it has more than one use case it belongs to the model but it bought this is the only use case I would say we’re a measure table or like a sub display folder makes sense I’d agree with that one and that but I wouldn’t put it anywhere else I would put it in this is very much out of the normal building of the report as long as it’s there I think it should be in the model again only if it exists or would live outside of that particular report or that use case

10:55 particular report or that use case are you not using like you’re I guess my question is are you not using conditional formatting very often no we’re talking about the report where does things live in the object like yeah I think I apologize I think I maybe went a little bit too deep a little too fast on a very specific part of conditional formatting I I get the report objects that we need to create right but the way you’re describing it is this way one-off like thing that you don’t put in reports but I’m saying I think it belongs and if we’re saying it belongs in the thin

11:25 we’re saying it belongs in the thin report I agree with that but it’s it’s a pretty standard object in my world like we have separate different things within a thin report that are specific for report visualization so it’s not like I agree we separated out in some way but it’s not like this anomaly that you rarely find in a thin report you saying like hey oh this is a very rare thing like no it’s a pretty standard thing report not the model yeah I gave it a

11:55 report not the model yeah I gave it a case statement so I do think that I think maybe I didn’t pick up on the if then or yeah you were still tangoing I know I get it yeah right my mind was elsewhere guys my my heart was on the Dance Floor you’re still John Travolta eating right somewhere around wow well what I would say is to that effect though desktop does not make it easy to format and again and getting a little bit of

12:25 and again and getting a little bit of History here Microsoft has definitely made the experience a lot better to be able to provide conditional formatting so previously when you would you’d had to go find things that were initially formatted you had to hover your cursor on top of the property for the effects button to even show up to be able to use it yeah I don’t know what that was all about but it was like hidden normally but then only when you needed it it was available to be there so so that was a big Boon to begin with because you had no clue what was configurable or not from a conditional formatting standpoint so that that’s a big mistake they fixed

12:57 so that that’s a big mistake they fixed that and now you can always now there’s nice a nice little square yeah and there’s a lot more properties now that you can conditionally format than previously so it’s a lot more areas of the report that you can do so for example I saw a LinkedIn post the other day which I thought was very interesting they had made a card and inside the card it was like a three or four visual objects all stuck together but the outline of the card the the perimeter they had a stroke attached

13:28 the perimeter they had a stroke attached to it and when it was normal a normal color the the entire card outline or the stroke around it was gray but when it went red or if there was any indication around red the words inside it changed from a green color to a red color and then the entire border of the kpi card lit up and turned red as well so I thought that was a very interesting use case like we’re talking about styling things that are not just the numbers or the colors or the numbers we’re talking about you can even take the Shader

13:58 about you can even take the Shader background of an object you can even take the perimeter of an object and this is maybe part of my gripe with Microsoft is I like that design style the challenge is it’s very difficult or I need to write a lot of extra Dax to get stuff to start triggering on this threshold here’s what this thing should do from a coloring standpoint so you use some I have to feel like if I’m going to do that pattern over and over and

14:29 that pattern over and over and over again and it seems to be again you said it Seth it’s common there should be an easier UI interface that is allowing you to Auto build these simple color flipping Dax statements and just put them in or cut it’s funny because it’s either custom visuals my favorite custom visual is card with states by SQL bi custom visual I replace that basically for any card that I have especially when there’s the context or we’re showing that I’m not I don’t use the kpi built

15:00 that I’m not I don’t use the kpi built in and to be clear it’s it’s card with States from okay this is who’s actually SQL bi as well okay I’m with you yeah but or the other side of the coin use the built-in feature scorecards which has the conditional formatting in a sense that you would want because what you can do with scorecards on a particular goal or a metric metric is you can say hey if we’re Above This value show green or shower

15:30 value show green or shower on track if we’re 80 percent or 95 you can specify the percentage difference yes and you’re not writing you’re not running a whole you don’t

15:38 you’re not running a whole you don’t have to write the Dax to do it no you just you just specify the parameters and it just works right rather than specifying a static number which really you’d have to write a static number for every measure to try to get it but now we can say just show me 90 if I’m 90 to the goal or to the Target value showed this particular status and that’s not built into Power bi report reporting that’s built in the scorecards and it’s built into some of this custom visuals so it does rely on the developer

16:11 visuals so it does rely on the developer to have a lot of experience or have a lot of real estate to create a lot of measures for one particular kpi but that’s one one side of the coin too sure so so your thoughts Seth what do you think about that you do you agree do you disagree disagree that there are challenges with creating all of this well where do you like where how like is it it’s probably not ideal today but where would you see how could we simplify that or what would in your mind what would make that easier for you

16:42 mind what would make that easier for you to manage to manage foreign foreign it’s a good it’s a good question because if if you think about what what we’re griping about right now it’s it’s having to stitch together all the components for a particular Visual and then doing it again or making adjustments for the same like a different one that corresponds with it so we have all of the grain we have to put it together every time yes and the proposal here is make it simpler so somehow allow me to not have to do like that grain level

17:12 not have to do like that grain level every time I guess where my mind is stuck though is do we get ourselves into trouble because of the nature of how we have to build these things with the specific tax calculations that we we have the capability of building right now right like it’s organization I I see possibilities of like getting into quick trouble of like which reusable object do I use like does do I get an interface

17:42 do I use like does do I get an interface where it’s like okay I want to create a set of condition like a conditional formatting group right where like everything I use should be like should correspond to the like this same object and then I get to reuse that that would make sense because I’m still like I do it once and then I get reuse out of it as opposed to having to put together every conditional formatted visual with the same components I just like in my head I’m like I don’t know how that would work out but yeah I agree like it but I agree with the standpoint that

18:14 but I agree with the standpoint that at some point I’d want to reuse things more efficiently maybe I guess what I’m saying but like what’s the difference between between like how to copy paste switch the visual type I I don’t yeah I just I’m not putting it all together well let’s take I think I what we deserve we need to do is take a step back we’re talking about all the complications here and all the extra steps well what is the real value of having conditional formatting why go through all the effort to put

18:45 why go through all the effort to put conditional formatting there in the first place yes yeah so I I would agree like that whole whole initially the initial indicator is you’re trying to highlight things of value to the user right you’re trying to draw attention to something that is and more important than others and this is is another another area here where you can so I’m gonna go off on a roof here for it for a little bit right

19:15 for it for a little bit right when you have a bunch of data on a table table especially when you’re thinking about the two things you want to consider is position and your direction right so position is where you’re at right now so for example I have a sales and I’m at 2. 5 million in sales today great that is my position that’s where I’m at right now the direction portion or the measurement of that position against a goal is what really indicates to me how am I performing this is what we’ve been talking about a lot of on the episodes is like goals and okrs or indicators

19:45 is like goals and okrs or indicators that tell me how close I am to what I need to beat towards what am I measuring myself to that is that is the indicator so now if you look at the direction right I am at 2. 8 million and I’m down three percent compared to last year or compared to last month that is now actionable because now I know where I’m at and then where I’m going I’m trending up or I’m trending down and now I can take action against okay that’s not the direction I want to be going at the position I’m at now I’m and then and so

20:15 position I’m at now I’m and then and so the reason I’m bringing all this up position and direction is because now this leads very well into okay now I need to talk about how do you visually indicate indicate with a glance where do I focus my attention there’s there’s too much data right in a report to know exactly which which data point is impacting my bottom line the most that’s the hard part and that and this is what we talked about in Prior episodes around the insights of information is a little bit more elusive

20:46 information is a little bit more elusive it’s not as simple as just creating a bunch of reports and and calculations right the the historical reporting is simple but insightful data is harder to come by yep I agree with that I I all of this is obviously for the end user but yeah additional formatting shines is quickly pointing out the areas that require immediate attention it’s not to say that everything in the report isn’t valuable to some degree it’s just even

21:19 valuable to some degree it’s just even when you think about when you attack a problem right what are the what are the things that are always asked to do by somebody in the organization just like figure out what the top bucket is right like what is what are my top 10 customers what are my top 10 employees what are my like that we have to solve this problem for and that’s what you go after right that’s what you go and fix and then you’re assuming that you’re gonna turn it green to some degree right like but that that’s the value in conditional formatting is where that

21:50 conditional formatting is where that that pops your immediate attention like Mike is saying and you can do that in different ways sometimes it’s a very straightforward it’s this table it’s this value however where it gets really valuable is where Mike’s outlining where there’s a lot of calculation done behind this to show you in general like where you’re going like yes like yes overall for very a large time frames like this last month this last quarter where what’s our where are we headed

22:22 where what’s our where are we headed right now and that better not be read otherwise people should be scrambling right to solve that bigger problem that isn’t going to be something that you can just rip in a spreadsheet go into a system and fix the data yeah right like those are big big initiatives that start to have to spool off and it’s all indicated off of conditional formatting yeah and I don’t know how many times I’ve said this on the podcast or if this is a hot take but I’ve really gone over the last two years where conditional formatting is not so much important as it’s required

22:52 it’s required and I think because it provides the two things that you said which I’m probably going to print out or just staple on one of my monitors position and direction oh I’m telling you it resonates with people yeah there’s a lot a lot of times people think about only the position sure and and that’s what a lot of bar charts we usually start with right here’s a bunch of positions I’ll make great directions where’s our Direction like and how does this measure against some standard like how do I know how far I’m like and and the analysis that I I’m gonna riffle your point here is coming because then I’ll sorry I didn’t mean to interrupt but I guess we went to Jazz

23:23 interrupt but I guess we went to Jazz from Tango so I got excited about your point that you’re but I agree a position and direction is incredibly important

23:28 and direction is incredibly important and with with allowing you yourself to explore more of this position Direction experience you now can say okay I have these three large items I now know if I add the the element of position I know I have three sales items that are selling the most in our company these are the top three sales products we have and the the position or the direction is now most negative on number item number two well then I Focus my attention there even though sales may be done on the on our best selling item if

23:59 done on the on our best selling item if our second less best-selling item has a 40 decrease in sales versus only a five percent decrease in your number one item well then don’t Focus there right the impact of what you’re going to adjust is you’re looking for where the largest amount of dollars now if you had a 70 decrease in your 10th largest product and you’re only selling a little bit of money around them then you don’t Focus there like that one’s just gonna naturally ebb and flow up and down don’t spend your time focusing on the product that’s only making you like a third of your profit or a tenth of your profit focus on the big ones Focus

24:31 your profit focus on the big ones Focus where you can move the attention on the on the large movers where’s the most money being lost from and most of the time when you are showing those types of visuals they need context correct and that’s the big part too where conditional formatting I think honestly if you I do have a few I will admit I have a few go-to conditional formatting that I really been using and they’ve been tried and true where there are the percentage decreaser increase or it’s a rate but that requires to me if that’s in any visual requires volume as a

25:01 in any visual requires volume as a conditional formatting from the gradient yes and because you have to provide that type of context to say yeah this has had the greatest growth but by far and away you are getting not the most amount of volume here it is one of the lower ones so don’t Focus here so helping see where those growth are but context is huge if you are going to tell a story and not just spit data on a report page it’s going to require you to show subsets of the data and that’s where context is so important yes so to me it is a

25:33 important yes so to me it is a requirement now in my reports so I’m gonna I want to jump in here as well so right off your point there Tommy oh sorry not jump in you’ve had enough jump in points well I want to ask this guy is like opening his mouth like okay I want you guys to go open up the data goblins article because what you just said Tommy is like verbatim I said Tommy is like verbatim again I gotta say it again Kurt mean again I gotta say it again Kurt Bueller is just killing it article after article after article is just amazing so the recent one here how to manage report

26:03 the recent one here how to manage report objects in a power bi data set is very relevant to this topic as well and Kurt’s got a great example of showing you here’s a semantic model the table that you’re going to produce in data calculations it’s going to be gross sales it’s going to be a dimension called producer it’s going to be a date column then he goes down even further and he shows you a real embedded in reports there’s actually an embedded report about third of the way down the article where you can interact with a report and he shows you he outlines all the different

26:34 shows you he outlines all the different objects a field parameter a dynamic title because that just showed up the subtitles of a visual is a new feature that was added in the last couple months so now you can have a subtitle that has a green or red as text and you can have additional text at that subtitle level the title colors can change he has year-to-date month-to-date indicators shading between the line an SVG bullet chart so he’s adding all these other visual based elements and I think it’s really interesting because

27:04 think it’s really interesting because what you’re talking about Tommy is this is exactly your point it’s giving you those positions and those directions and highlighting various elements and I think it’s a very written very well written article but it also outlines what are what are the things that I’m using to style this report to provide additional action so I’m sorry I didn’t mean to interrupt Seth go ahead no that’s good finishing the thought in a in a good way I wanted to I wanted to sidebar on something that you were saying in terms of like volumes of data

27:34 saying in terms of like volumes of data conditional formatting oh yeah and and the direction of where where we head in the sidebar is this like for a lot of us that are dealing with data on a large basis conditional forming is typically used in tables right large volumes it could be large volumes of data and where I find I find people get hung up in solving business problems like hey we we’re looking at this table of information because we’re identifying areas of a problem that we’re going to go solve in some other system or with the customer or whatever

28:05 system or with the customer or whatever these may be sure and and what happens to a lot of folks is they they look at these really long lists of like thousands of customers or thousands of employees and then they’re going to find something and and they they sort and they start and they just start going through every single one of them into Mike to your point this is the absolute worst thing you can do in in any analyst role where you’re an individual contributor and yep you’re you’re tasked with solving business

28:35 you’re tasked with solving business problems because what happens is you immediately go down to the finest grain and think that you’re you’re making an impact by solving a problem on a row by row basis and the fact of the matter is is you don’t know great observations then you get into a conversation where you’ve been working for three weeks with your boss and he’s like what’s the impact we made and you start going through I’ve gotten through 150 rows and the first question they should be asking is is okay okay but what is the priority like what was

29:06 but what is the priority like what was the priority yeah how did you attack that right because as you go through these things you should always bucket them in in the way that make sure that you’re making the business the largest business impact based on the work that you’re doing and many times of those 2000 records that you need to go solve it’s going to be a very small number that are going to impact like 80 percent of what you’re trying to do correct you did 20 you would have finished out 80

29:38 did 20 you would have finished out 80 and in that three week time all of the remainder of that is just like the other noise sometimes yeah by the time you get to a meeting it is much better to be in a position where you’re like I have now solved 90 of this business problem do you want me to spend the next 1800 rows because I’ve only gone through 200 to fix the other 10 yeah that is value does this mean you’re doing this analysis and conditional formatting bucketing these things into a realm of

30:09 bucketing these things into a realm of solving these problems always look in the context of position Direction but business impact as well to your point this is a really good I don’t want to say stop Gap but a fork in the road we’ve said the importance of conditional formatting but it’s it’s really important not to conditional format for the sake of conditional form oh my gosh rainbows rainbows throughout everything yeah I would agree with that yeah don’t take this and stop the podcast listen to the

30:39 this and stop the podcast listen to the end and say oh I should add it to everything no and why well because to your point there’s no context or position and direction if you’re just adding random colors there’s no story you’re not framing anything yeah because the priorities your point it becomes noise yes yes right cognitive load goes up 100 agree like when using conditional formatting make sure it’s limited right to the Park area yeah targeted is

31:10 right to the Park area yeah targeted is a good word because then it’s meaningful right more colors you throw onto it Excel spreadsheet all of a sudden the less meaningful it becomes and and what

31:19 less meaningful it becomes and and what it what am I supposed to do with five columns right a little red green yeah or everything’s a gradient 500. that’s a great point and gentlemen allow me to raise my hand as the first to say how many times I’ve done that so yeah where did that came out but your point Seth brings to me another line of thinking that is very close to what you were saying it’s and it’s this idea of measuring the size of impact of what you’re producing against the data like and what you’re working on right I remember there was a project I think you and I are working on we’re loading a

31:49 and I are working on we’re loading a bunch of data out of a system we’re trying to look at data quality we’re trying to say do we have any missing products and I remember going through this and thinking I feel like we’re getting a lot of them there’s only a handful of things that we’re missing we have people saying we’re missing products things aren’t updated things what’s going on so I remember going through and doing an analysis and I was like okay let’s talk about let’s talk about this the size of stuff that we’re talking about here like let’s talk about the impact of what we’ve been doing hey we ran an analysis yes we had we had

32:19 we ran an analysis yes we had we had like like 99. 8 or 99. 5 I don’t remember the number but it was very high number we had like 99. 5 percent of all data captured from operational systems inside our data warehouse thing and we’re like look the the issues we’re dealing with is less than like a half a percent of stuff that we’re having problems with so let’s focus like I understand there’s potential still challenges but like again this is this is another analysis that I work with with customers is like look if you want 100 data quality all the time that is a lot of money so it’s

32:51 the time that is a lot of money so it’s it’s like a it’s like an it’s like a exponential curve right 100 growth or 100 clean data depending on the size of your data we’ll probably never get there you could potentially get yourself 100 there like Financial systems have to have it but you’re going to spend a whole lot more money making sure that every single thing ties out to the most fine-grained detail and everything every single thing is captured and again you may be in an industry where you need that on the other hand the business has to make a decision is what threshold of

33:23 to make a decision is what threshold of acceptance of junk data are we going to make in order to allow us to run our business right so this is where again I don’t want to go back to data Ops and data quality things but this is another impact of that is also very important because at some point in time you have to say we can run our business on 98 clean data that’s good enough now if that if the threshold dips below that amount amount we have problems someone needs to stop what they’re doing and go fix the data because now we’re introducing challenges and issues with information if you’re

33:54 and issues with information if you’re above that threshold fine keep running on business as usual because then I’m not focusing my time on this such minute thing that’s not really adding impact and value and value that’s a real real example like that’s like my real example of like why this is important and I I’ll subset off of that because you said one of the words there is a great place to start if you want to incorporate conditional formatting but you don’t have a place to start and that’s threshold yes and trying to figure that out with the user where they go we don’t

34:24 out with the user where they go we don’t really have kpis it’s like okay if you’ve got to have something if your Department’s operating at 10 is that a problem yes is it 20 is it 30 yeah exactly you gotta walk him there walk them to where they will be a little worried if they’re then they’re gonna be like well we want to be 100 we’re like well that’s unrealistic yeah that’s great cool yeah that’s great you’re living on Cuckoo cloud like that’s not gonna happen but it’s not happening yeah but but just trying to walk people and understand like and one I I know I’ve been saying this a few times but everybody has every Department every

34:54 everybody has every Department every organization has pressure points yes and bad and trying to walk them to those in a sense those I don’t say extremes but near those extremes to warn them and that’s if they don’t have their red and greens for the because they don’t have a okrs there’s still something that person’s going to want to know or be alerted by and I think we have to do a great job walking them there all right I we’ve been agreeing too much on this topic we need to we need a little bit of controversy they just call it a win to spice it up a bit here we’re

35:25 it a win to spice it up a bit here we’re we’re doing the ball we’re doing the ballroom dance of Waltzing we’re Waltzing around we need some spicy in this I think you’re if you’re looking for for some punk yeah I’m looking for some I’m gonna ska dance at this point okay let’s go do some sky dancing so what colors do you use when you’re indicating things I think I think everyone starts with Red Green Red Green as good and bad because that’s very well known from like a stoplighting perspective are there other default colors that you you lean on

35:56 you lean on that are good indicators of highlighting something I generally use the same color background when it’s good when it’s bad so they don’t know so it’s like white on white yeah no well it I think the red and green is fine when it comes to selling good bad but I think it this is one of those that depends on what you’re trying to show if it is a volume it’s gradient it has to be gradient and it has to be a one color of that

36:27 it has to be a one color of that gradient what this is where I think I’m gonna disagree a little bit I I don’t think I think red green is good okay but I think I would I think I would err on like red gray and some other like green gray and some other color like doesn’t matter what but something else that’s like not right I don’t really love red the one it feels a lot like Christmas just by by that but then there’s also this study that’s like if you have people that are colorblind red and

36:58 have people that are colorblind red and green can sometimes be a little bit harder to distinguish don’t use the ketchup red psychologically we know red does mean negative correct it’s an offset of the red unless your brand is like something like Milwaukee tool where red is your brand so like you’re I feel like everything’s in red you’re like oh that’s actually our brand name like that’s what we do so like this this is where I’m also it’s but this is sure but this is where I’m trying to say like the colors matter in this respect in this perspective and so like how do like I think orange or yellow also potential but yellow is very

37:29 yellow also potential but yellow is very hard to see against other colors so like if it’s not on a dark background yellow doesn’t pop to me very well so like I don’t I wouldn’t yeah I I don’t know this is where Blues I think or dark Blues seem to make sense in some fashion to and then like a brighter color that’s against that that like an orange or like a anyways I would just there’s a lot of interesting color theory things have you looked at you would never use blue or green for bad no nothing eliminate those but I’ve never used once again blue or green why

38:01 never used once again blue or green why eliminate them from what from the negative or bad for yeah those would not be negative colors I would indicate yes yellow maybe orange you said is too hard to see I it has to be on that Red Spectrum it doesn’t have to be the right not necessarily red no no it’s not eight like there are let me hear you because you said some other color so elaborate so if we’re gonna look this up quickly like the general principles are cooler colors are are don’t raise

38:31 cooler colors are are don’t raise your blood pressure right warm colors do right so like Reds the reason you don’t plaster red all over everything no matter if you’re Milwaukee tool or not is it like it elicits a response and somebody’s looking at that of like angst right cool colors don’t do that so in general I would say if you’re going to have a three color spectrum on we’re good we should watch something you need to be concerned about something you would walk

39:02 concerned about something you would walk up that color spectrum of cooler to warmer colors I do use in that three

39:07 warmer colors I do use in that three thing I I use blue like typically rather than use red yellow green I’m a blue yellow orange because like red red would get like yeah it’s it’s off the charts it’s it’s the worst thing in the world but because of the sensitivities of some users not being able to pick up all that color spectrum I try to use something but that’s my default but I would say in general like you can use like different

39:37 general like you can use like different colors colors but it’s the higher you go up in that spectrum of like heat is typically where and I don’t know if there’s one better than red but the closer you get into that range the more that elicits the like this is not I don’t feel comfortable it’s not a good spot like yep yep I don’t think you can take that away to your point Seth I don’t think you can take that color the warmer colors definitely elicit a different emotion than the cooler colors I think you’re 100 agree there’s a psychology part to

40:07 100 agree there’s a psychology part to this as well however what I would maybe argue is I don’t necessarily need a two ends of the spectrum color and maybe my argument here is a little bit around I’m thinking maybe you mute like okay what are you trying to show are you trying to show things that are out of tolerance or out of out of spec well everything that’s intolerance things that I do not care about is more of like a gray or muted or just a black color right there’s really no color and so I would even like play with the idea of adding color when needed and then

40:37 adding color when needed and then removing it when not and not necessarily going for like a green red instead going for for red only red gray or or a diverging color along those things or Green gray thing these are good things and these things that are not good right and using a pair like that a little bit how many how many times in your data is that is it so black and white though right like either we’re good or we’re bad typically it’s a I have my highest

41:07 bad typically it’s a I have my highest priority things we need to fix immediately these secondary list of things are just as in like are important as well but we have to do these things first like typically every problem I solve or have to within the org data related business related Etc always falls into that Spectrum which is why to me there’s always the three buckets because and I agree maybe you don’t need the third bucket it’s just white like or it’s fine or you don’t need to concern yourself with it the only things we have we care about are these two because it’s

41:39 we care about are these two because it’s correct Priority One or priority two and rarely do we ever get the priority three there’s always going to be a priority one or two the important aspect there too is only I would only use different colors when it is something where it is a an increase or decrease or meeting that goal if you are showing simple volume difference that should be on the same color spectrum the same color I would not change colors there so if you’re showing I’m sorry volume difference versus what versus let’s say a below or above a

42:11 versus let’s say a below or above a Target Target oh you’re you’re talking about gradients so using gradients when you’re talking like a spectrum of colors versus like a single yes right that has to be the same color especially when you’re dealing with bar charts or or even other numbers unless there again unless there is a some there’s a threshold there’s a particular number line threshold where you’d want to see that above or below so anything else should stick on the same color I think what you’re referring so and my in my Envision of what you’re talking about

42:41 Envision of what you’re talking about here is you’re you’re leaning on you’re leaning on a bit more around like the individual colors themselves and so if I have a goal or Target we can we can go after the goal or Target and then more toggle it on or off right if you don’t have a goal or Target and you’re looking like a at a variety of numbers spectrums or or diverging colors palettes make more sense and I would agree with that too yeah the color yes different color yep yeah I agree actually I’m gonna I’m gonna backtrack a little bit Mike because I I think the

43:12 little bit Mike because I I think the way way or or I should say clarify you said two colors and I don’t know if you were describing or I took it in the spectrum of like of like best worst or or black white thing okay I do agree in a two spectrum color where you’re just identifying the bad stuff yeah and there is no color for the for the like things we don’t care about it would only be highlighting the things that we do care about yes yes I think there’s the distinction there I agreed yes there’s a lot of and

43:43 there I agreed yes there’s a lot of and this is where we talk about and I’ve talked about this a lot before in the podcast is imagine your data model being a very large table with millions and million rows in it every row is not important a handful of those rows are important you’re trying to highlight those handful of rows that get people to take action on something that makes those rows impactful for what you’re trying to produce produce I think this is a a good this is a really good topic I think we’ve really gotten into it we might have to come come back and do this again so as we wrap here I’ll give you a quick a couple

44:14 wrap here I’ll give you a quick a couple items so it’s chat GPT time we’re gonna ask chatgpt what they think and restore it so what are the best ways to use color in business intelligence is the question question use color to highlight key insights use color to draw attention to important data points I thought that was good use consistent color scheme I also agree with this one use a color scheme across all your visualizations and complements each other across visuals I think it’s a great point use contrasting colors use colors to differentiate between data points and

44:45 differentiate between data points and that was what we were saying like pick two ends of the spectrum whatever color you’re picking and it says here also we talked about this one as well use color sparingly avoid using Too Many Colors as it can lead to confusion and make users have difficult to interpret the information stick to a handful or a few colors that complement each other easily to distinguish between them consider color blindness was the number six point so chat GPT is accessibility enabled so it says take

45:18 accessibility enabled so it says take an account colorblindness designing your visualizations I thought that was pretty good I think they’re pretty good I get about a five better than better than normal five out of seven yeah five out of seven yeah set screw in a five out of 10. 3. 5 out of seven [Laughter] right in the middle like Barbie’s like this this is also not robots so exactly we don’t have the entire internet in our head that’s that’s just how it works trained as before 2021. all right with that thank you all

45:48 all right with that thank you all very much for your time and your listenership we really appreciate your ears on this one so our if you like this idea or this concept if you like some of the Articles all the Articles we talked about will be in the description below feel free to hit those links and check out these articles and other interesting things that we found around colors as well and confessional formatting we’ll probably revisit this one again so I think this is a good topic Tommy where else can you find the podcast we can be found on Apple and Spotify make sure to leave a rating helps us out a ton if you want to join the conversation live you can every Tuesday and Thursday 7 30 a. m

46:19 can every Tuesday and Thursday 7 30 a. m join the community and finally ask a question go to Power bi tips the podcast and we have a mailbag and maybe your question or topic can be featured as one of our episodes you can go to Power bi tips podcast and ask your questionnaire we have a little teams form there you can fill out and we’ll get it funneled into our topics thank you all very much and we’ll see you next time

46:56 thank you [Music]

Thank You

Thanks for tuning in! If you enjoyed this episode, please subscribe to the podcast and share it with a colleague.