cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
evanderpoel
Frequent Visitor

Data colors on chart based on rank

Hello - I am trying to color the bars on a chart based on the top 3rd, middle 3rd, and bottom 4rd of a measure. The chart I'm using is below and if there are 99 items, I would like the top 3rd to show as one color, the middle 3rd to be another color, and the bottom 3rd another color. The number of items and measure values will change based on the time and geography filters the user selects - so they will not always be the same. Because of this, I don't believe it can be done with the rules available as I would have to use values to dictate the colors. Sincerely appreciate any help that anyone can provide! Please let me know if there is any additional information I can provide to help get this solved. Thank you!

 

Table: Timeframe by SKU

Axis field: Flavor

Values: U/Str/Wk

 

evanderpoel_0-1618773537615.png

 

1 ACCEPTED SOLUTION

Hi, @evanderpoel 

Thank you for your feedback, and sorry for the misunderstanding.

Please check the link down below, and please let me know if I understood correctly this time.

 

https://www.dropbox.com/s/p2iny0qysrzhc4j/evanderpoel.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

9 REPLIES 9
Jihwan_Kim
Community Champion
Community Champion

Hi, @evanderpoel 

Please check the below picture and the sample pbix file's link, whether it is what you are looking for.

All measures are in the sample pbix file, and the steps are numbered in front of each measure.

Top: Red

Middle: Black

Bottom: Yellow

 

Picture4.png

 

https://www.dropbox.com/s/p2iny0qysrzhc4j/evanderpoel.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi - Is it possible to change the formulas to say the top, middle, and bottom third of skus rather than top, middle, and bottom 3? For example, when there is 99 items, the top 33 would be one color, the middle 33 would be another color, and the bottom 33 another color. If the user change the filters and there are now 90 items, then the first 30 items would be one color, the next 30 items would be another color, and the bottom 30 items would be another color. Please let me know if this doesn't make sense. Thanks!  

Hi, @evanderpoel 

Thank you for your feedback, and sorry for the misunderstanding.

Please check the link down below, and please let me know if I understood correctly this time.

 

https://www.dropbox.com/s/p2iny0qysrzhc4j/evanderpoel.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

You could probably write a RANK measure and color based on that.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Do you have what the rank formula would be? I don't typically write formulas in powerbi, so I'm not sure how to go about doing that. Happy to send you an example of my powerbi file. Thanks!

Hi,

I can try.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
evanderpoel
Frequent Visitor

I don't think it could be dynamic though- I would have to do the work to split the measure out by three across each geography and each timeframe since the measure top / bottom will be different across each filter. I was hoping someone could write a function script to accomplish this to avoid manually doing with each data refresh. 

selimovd
Community Champion
Community Champion

Hey @evanderpoel ,

 

you should create 3 measures that are doing a dynamic segmentation to the desired areas.

Then you can add the 3 measures to the same graph and give each measure a different color:

selimovd_0-1618782678008.png

 

I think that's the only way how you can achieve that.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Do you mean create the measures in my original excel file or create them in within the powerbi file? Do you have the formula for what each measure would be? Happy to send you an example of my powerbi file if that would help.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors