cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jagostinhoCT
Post Partisan
Post Partisan

Custom Data Colors using Function

Hello,

 

I am trying to set custom Data Colors for a bar graph based on their values.

I would like to visual to show something like this, i.e. all values < 25% to be green,>= 25% to be amber and > 75% to be red

jagostinhoCT_0-1641982093200.png

but I am getting this result instead. I feel that I am not adding the Rules correctly but I am not able to detect what am I missing.

 

jagostinhoCT_1-1641982202991.png

 

Thank you for your help.

 

 

 

2 ACCEPTED SOLUTIONS

@jagostinhoCT , Try to create a measure like 

 

Color =

Var _1 = [sum of BM Quantity] // make sure this the ratio measure 

Switch( true() ,

_1< =.25, "green",

_1< =.75, "yellow",

"Red"

)

 

Use in conditional formatting using field value option

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

Hi, @jagostinhoCT 

Here you should use 'value' instead of 'percent'. Percent in conditional formatting refers to the percentage of all data distributions. Please refer to this document for details.

 

ypng.png

You can also refer to my sample file:

y1.png

y2.png

Best Regards,
Community Support Team _ Eason

View solution in original post

9 REPLIES 9
Mayank_Yavda
Resolver II
Resolver II

Hi @jagostinhoCT,

Here, is bar chart with custom colour formatting based on percentages it will you to get colour formatting on bars.

 

Bar chart with custom colour formatting based on percentages, PBIVizEdit.comBar chart with custom colour formatting based on percentages, PBIVizEdit.comBar chart with custom colour formatting based on percentages, PBIVizEdit.comBar chart with custom colour formatting based on percentages, PBIVizEdit.com

 

 

 

 

 

 

 

 

  Download link for the custom visual file in this page,

https://pbivizedit.com/gallery/bar-chart-with-colour-formatting-based-on-percentage 

 

This was made with our Custom Visual creator tool PBIVizEdit.com. With this tool,

  • anyone, irrespective of technical skills, can create their own visuals
  • 15 minutes to create a visual from scratch
  • opens up many additional attributes to edit (for e.g. labels, tooltips, legends position, etc)

Give this a shot and let us know if you face any problem/errors.
You can use the editor to modify your visual further (some modifications cannot be done in Power BI window and have to be in editor).

Thanks,
Team PBIVizEdit

jagostinhoCT
Post Partisan
Post Partisan

sample data column

 

ProjectsDB.ProjectAuditIDProjectsDB.BIM Quality Score
433LOM_BA040.00%
433LOM_BA059.68%
433LOM_BA0626.00%
433LOM_BA0730.00%
433LOM_BA089.00%
433LOM_BA0925.00%
433LOM_BA100.00%
433LOM_BA117.00%
433LOM_BA1215.00%
433LOM_BA1316.00%
433LOM_BA1415.64%
433LOM_BA157.00%
433LOM_BA160.00%
433LOM_BA1715.00%
433LOM_BA1815.00%
433LOM_BA1915.40%
433LOM_BA2056.00%

Hi, @jagostinhoCT 

Here you should use 'value' instead of 'percent'. Percent in conditional formatting refers to the percentage of all data distributions. Please refer to this document for details.

 

ypng.png

You can also refer to my sample file:

y1.png

y2.png

Best Regards,
Community Support Team _ Eason

thank you very much @v-easonf-msft for such detailed but very clear explanation. I certainly learned something new with your post.

amitchandak
Super User
Super User

@jagostinhoCT , if you column is Ratio/percent then you should use number in the column where you have taken percent

 

Also usually the ratio is below  1 unless you multiple it by 100

so 25 % is .25

 

based on this do the changes and try

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

hi @amitchandak ,

 

the values are formatted as percentages.

the custom colors are still not working regardless of being set as percentages or numbers.

 

Thank you for your help.

jagostinhoCT_0-1641984338712.pngjagostinhoCT_1-1641984400610.png

 

jagostinhoCT_2-1641984408099.pngjagostinhoCT_3-1641984415695.png

 

 

@jagostinhoCT , Try to create a measure like 

 

Color =

Var _1 = [sum of BM Quantity] // make sure this the ratio measure 

Switch( true() ,

_1< =.25, "green",

_1< =.75, "yellow",

"Red"

)

 

Use in conditional formatting using field value option

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

thank you @amitchandak 

 

This measure was key for the answer to my query.

I just needed to understand that I needed to add a "return" before the SWITCH function.

 

The final measure formula, for reference was thus

 

Color = 
VAR color = MAX(CurrentProjectAudits[ProjectsDB.BIM Quality Score])*100

return
SWITCH(TRUE(),color <= 25,"green",color <= 75,"#FFA500","red")

I could not find a way, however, of "just" getting the value in a cell without using some sort of functions (I used MAX above).

Could one not just use something like:

 

var color = CurrentProjectAudits[ProjectsDB.BIM Quality Score]

I am getting this error when pasting the code you sent me.

 

jagostinhoCT_0-1641985598073.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!