Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwalter10
Helper I
Helper I

color management advice

Hi, I'm looking for an comprehensive way of controlling the colors that appear on my graphs other than manually selecting each color for a particular value.  Maybe there is a tutorial somewhere?  

 

In a perfect world I would be able to specify colors in a table that would match the identifier names in the data, so that for instance, every time that I am reporting on "12x12 Plastic Sales" it would come up #xxxxxx, and "12x12 Plastic Units" would still come up #xxxxxx, and the same with other elements matching to particular colors.    

 

the way it is now, I can have two visuals on the same page, one for sales, one for units, and unless I manually go in there and pick colors for each data set, I get two arbitrary sets of colors.  Worse, if I filter out some data points, the colors will shift.

 

I know this isn't a straightforward solution, just hoping for some links for possible answers.  My data sets have hundreds of possible items to choose from, thus having a lookup table would be great.  

 

Thanks!

 

See attached for samples of what im talking about

SS 01-13-21 at 01.57 PM.jpg

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey @jwalter10 ,

 

I wrote the chapter "CHAPTER 8 – Using DAX to solve advanced reporting requirements" that covers exactly your question.

Maybe you want to have a look at the book @Phil_Seamark and I have written some time ago:

 

As the solution is not that simple, please excuse that I do not simple copy the chapter (next to some legal considerations):
Pro DAX with Power BI: Business Intelligence with PowerPivot and SQL Server Analysis Services Tabula...

 

The simple product table:

image.png

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

parry2k
Super User
Super User

@jwalter10 see attached and you can enhance it from here...The color measure is the key, it doesn't need to be part of the table the way I did it but having it in the product table will make it easier.

image.png

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

17 REPLIES 17
parry2k
Super User
Super User

@jwalter10 What visual is this? Do you have multiple measures? Do you have anything on the legend?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

hi, attached is my stripped down PBIX, see if you can see how to change it to present the functional color button.  i dont see how to add it as an attachment, here is a dropbox link: https://www.dropbox.com/s/ajcqwh7t4p2nb6q/JW%20color%20test.pbix?dl=0 

parry2k
Super User
Super User

@jwalter10 Yes I expect conditional formatting will be everywhere and happy that there is a workaround. All in all, Power BI is still a young tool and we need a lot more improvement, but having a release every month, makes it a super exciting tool to use.

 

I hope this solution works out for you for now before it is all readily available in Power BI. Good luck!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@jwalter10 see attached and you can enhance it from here...The color measure is the key, it doesn't need to be part of the table the way I did it but having it in the product table will make it easier.

image.png

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks Parry, 

I like your solution, seems pretty straightforward to add a color field to my product table.  I like how it appears to work with the pie chart as long as you start off assigning the function to a bar chart version before switching to another style...seems like a future release would enable conditional formatting for all charts, no? 

parry2k
Super User
Super User

@jwalter10 you can put a logic on the product, I'm going to try to create a sample at my end.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TomMartens
Super User
Super User

Hey @jwalter10 ,

 

I wrote the chapter "CHAPTER 8 – Using DAX to solve advanced reporting requirements" that covers exactly your question.

Maybe you want to have a look at the book @Phil_Seamark and I have written some time ago:

 

As the solution is not that simple, please excuse that I do not simple copy the chapter (next to some legal considerations):
Pro DAX with Power BI: Business Intelligence with PowerPivot and SQL Server Analysis Services Tabula...

 

The simple product table:

image.png

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom, 

Thanks for your suggestions.  I did get your book and see how you are able to determine a color based on values within the data, but I am not seeing where to apply it to my visual.  The conditional formmating tab is only visible in select cases, as seen in Parry's example I downloaded.  It appears to be ok if i only select 2 of the three values going into the visual (axis and values), if i select the third, and with any combination, i lose the conditinal formatting ability.  Am I missing something?  almost all of my visuals have a time axis, a legend (products that i want to control colors for) and the values for each product.  Nothing i can see will give me that conditional formatting capacity.  Is this coming in a future version?  mine is currently desktop Version: 2.88.1144.0 32-bit (December 2020) 

also sorry for the messages getting out of sync

conditional formatting tab available and linked to the color fieldconditional formatting tab available and linked to the color fieldworking conditional formatting, but only 2 of 3 data points specifiedworking conditional formatting, but only 2 of 3 data points specifiedadding the 3rd data point breaks the conditional formattingadding the 3rd data point breaks the conditional formattingNotice the conditional formatting tab is now missingNotice the conditional formatting tab is now missing

Hey @jwalter10 ,

 

unfortunately the ability to apply conditional formatting is dependent of the dimensionality of the data assigned to the visual.

As soon as the legend is used or has been used, the ability to apply a conditional formatting disappiears.

 

I have no idea if this will change some in the future.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

thanks, I appreciate that this is a bonus to be able to do this in this ongoing and developing PowerBI, even though its been a staple of Microsoft Office for years.  Still, it feels kind of worthless of an option if it only works with two dimensions of a visual and not three which would be typical of most visuals, i would think.  Hopefully that is something that can be addressed.

 

It really comes down to having no control over the data colors except for painstakingly finding each item and assigning a color.  What is really hard is there is no consistency...in the original post, i showed the top three with the same colors (becuase i had copied the original and just had different parameters), but as soon as I put a brand new visual it comes up with an entirely different set of colors.  Another issue is that if the data changes and a new product is included (say because it was a more recent addition), it throws off all the existing colors.

 

 I guess i don't understand what the default behavior is in order to control it better.  Anyone else have this problem?

 

thanks, i think your solution might do it.  I definitely dont mind if its complex, esp if i can anticpate the need and build the table in code beforehand.  I will take a look at your book, let you know if it works

I also am hoping a solution can give me controls on the colors throughout my diagram, its been really difficult to figure out how colors are assigned and requiring manually selecting them each time is a real time waster.  Esp if the data shifts and all the colors change.  My goal is to have these reports built month to month, and having a single color for a particular data item fixed between months will go a long way towards readbility. 

 

 

parry2k
Super User
Super User

@jwalter10 conditional formatting would have solved this but seems like that is not available for pie chart. Maybe first create a bar chart, apply conditional formatting by using a field value and then convert the bar chat to pie chart. Read more here about conditional formatting.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi Parry, 

I got your example to work fine, changing colors by just changing the hex value in the table.  But when i try to use this on my existing pbix file and visuals, I dont get the Fx prompt, just the existing colors.  is there an update that i need to do on the file itself?  the picture on the left is my existing visual for a bar chart, one on the right is your visual in a different file, showing the fx button.  Current PowerBI version is 2.88.1144.0 32-bit (December 2020)

edit: same situation with a brand new file, the fx button doesnt show

thanks!

 

screen shot of two versions of data colors in two different files, same type of visual.jpg?  

thanks for your suggestion, but its not a conditional on the value of the item, but the identifier/name.   So Product 1 would always be red, even if i was specifiying it as the sales value or units.  The actual values of each are not important, just that Product 1 is always red.  Product 2 always blue etc.  

Allowing default color values to be specified for a given dimension is essential. Tableau has this exact feature under "Default formatting" for a given dimensional field. And the default behavior in Tableau is to assign only one color for a given dimension value (though color repeats are possible if it's a high cardinality dimension). 

That it's even possible to encode the same discrete dimension with different colors across charts is just bad visualization practice. That's why it's crazy that PowerBI even allows this -- it's even crazier that it's difficult to avoid this confusing design.

Aside from recommending Tableau, which I tried using and didnt like, i agree that the color management in Power BI is awful and not consistent at all.  I have about 20 visuals in my file, and zero agreement on the colors used.  Also they change if I add a new data point.  very frustrating and not productive at all

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.