cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Bar chart / Stacked bar chart / pie chart color based on database field?

Hi all, 

 

I would like to control the bar and pie chart colors based on a attribute in my dimension tables.

I created "color" field in the dimension for my categories and now I want to use the color value from the database to be used in my chart.

I've looked at the condition formating but it's not allowing me to achieve what I am trying to.

 

in the "Data colors" I want to provide the color for each category from a field in the database.

 

I have many report and many charts in the reports using the same categories and I want to control the colors for each category across all reports/pages/graphs.

 

Any advice on how to achieve this would be great.  Maybe I am missing something obvious here.

thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @crenninger 

I have a test and I found the result like yours:

We can't use fx in stack visual if we add legend , and we can't use fx in donut and pie visual.

You may vote up an idea for this function: Condition formatting for measure

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

8 REPLIES 8
Highlighted
Solution Sage
Solution Sage

Hi @crenninger 

Please send sample data or file. It will be easier to help you solve the problem.

 

Generally speaking, your thinking is good. Ideally, you would create a measure that checks what category is currently selected and automatically assigns a color from the column that lists the colors.



_______________
If I helped, please accept the solution and give kudos! 😀

Highlighted
Helper I
Helper I

Hello

You tried conditional formatting and relied on the number column as such evaluate it and select the color?.

I share the following links:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

https://community.powerbi.com/t5/Desktop/how-to-make-chart-be-colored-based-on-the-value/m-p/1367106...

Ss

Highlighted

This is not exactly what I am trying to do, remember I am looking for Bar char, Stacked Bar chart and Pie chart.

 

Based on a simple table in PBI  see screen shot on what I am trying to do.

 

I can't find how to attach a PBIx file to this post unfortunately

Here is the table

crenninger_0-1600296754631.png

 

 

I hope this helps explain.

Capture.JPG

Highlighted

Anyone had an idea or is this just not possible?

Highlighted

Hi @crenninger 

You can try customize theme, and change the color code in Themes colors.

1.png

2.png

Result is as below.

3.png

You can download the pbix file from this link: Bar chart / Stacked bar chart / pie chart color based on field

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Highlighted

Hi @RicoZhou 

 

thanks for the insights but this doesn't help driving the colors from the database as far as I can see.

It's only picking the colors by the "indexed" value which unforntunately can change depending on the filters selected.

 

I made some progress using conditional formating but that only works when the "legend" is not entered in the graph.

When you don't specify a legend, in the "Data colors" you can choose "fx" and in conditional formating select "Field Value" then "First country color" And "First"

 

this works on "single" Bar charts without legend but for example on a stacked chart where I'd like to control the colors within each bar the "fx" option disapear.

The "fx" function is not available at all in pie or donut charts either.

 

 

Highlighted

Hi @crenninger 

I have a test and I found the result like yours:

We can't use fx in stack visual if we add legend , and we can't use fx in donut and pie visual.

You may vote up an idea for this function: Condition formatting for measure

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

Highlighted
Community Support
Community Support

Hi @crenninger 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. And please vote up the function you want in Idea.

 

Best Regards,

Rico Zhou

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors