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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mzienowicz99
Helper I
Helper I

Adjusting colors to the data in the legend

Hello!

 

For the last few hours I started digging into the topic of changing colors in Power BI. I was introduced to ,,advanced controls" in the visuals and much more staff, but nothing coming from that research could work with my project.

 

The task that I'm working on right now contains of creating the map which shows leader brands in individual countries. I created a table which was divided by country and by brand and I also ranked market share (by rankx) which than gave me a leader for every country. Then I changed the table to Filled map and it worked perfectly and map also acts great with changing filters.

 

The only issue I have is the color of leader brands shown on the map. I need to be really strict and have the country coloured in an exact colour of the brand by HEX code or RGB of the official brand, and not have something like this:

 

mzienowicz99_0-1638368303498.png

 

So let's take car market for an example. In Italy the market leader is Ferrari and I want the country to be coloured red because it's an official color of brand Ferrari and not violet like it's shown on the map. Anticipating the questions I saw that Power BI let users to use advanced controls in Data colors formatting but in my case, in which I have to use a legend from a column in a table, the option of advanced controls just doesn't appear. I also know that I can change colors manually by just pasting the HEX code to different brands, but the case is that I want to standardize colors of individual brands for every visualization in this power bi sheet. F.ex. If I create a bar chart, the brands there will have exactly same colors as on the map. 

 

Is there any way to do such a thing, by some measure or making a table with colors? Thank you in advance for your help 🙂

1 ACCEPTED SOLUTION

Hi @mzienowicz99 ,

According to your description, you can modify the formula like this:

Rank =
RANKX (
    FILTER (
        'Car',
        'Car'[Brand] = EARLIER ( Car[Brand] )
            && 'Car'[Month] = EARLIER ( Car[Month] )
    ),
    'Car'[Market Share],
    ,
    DESC,
    DENSE
)

In this way, the rank of market share will be depending on the month.

vkalyjmsft_0-1639470596677.png

For bar and line chart, it also works fine. 

First, put country in axis, market share in values and rank in visual filter, then set rank is 1.

vkalyjmsft_1-1639470738120.png

Also click fx in Data colors, and use the color measure.

vkalyjmsft_2-1639470893603.png

vkalyjmsft_3-1639470969183.png

Get the expected result, and the month should be putted in a slicer.

vkalyjmsft_4-1639471014328.png

 

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

7 REPLIES 7
v-yanjiang-msft
Community Support
Community Support

Hi @mzienowicz99 ,

By my test, if there is a value in legend, the fx icon in Data colors will missing as you described.

vkalyjmsft_0-1638784736860.png

 

vkalyjmsft_1-1638784736862.png

 

Heres my solution.

1.I create a sample by your description, Create a rank column by the market share as you mentioned, then brands with rank equal to 1 are the leading brands in this country.

Rank =
RANKX (
    FILTER ( 'Car', 'Car'[Brand] = EARLIER ( Car[Brand] ) ),
    'Car'[Market Share],
    ,
    DESC,
    DENSE
)

vkalyjmsft_2-1638784736866.png

 

2.Put country in the location filed and put rank in the visual filter, then select rank is 1.

vkalyjmsft_3-1638784736879.png

 

3.Create a measure to define to the color of different brands.

Color =
SWITCH(TRUE(),
MAX('Car'[Brand])="Citroen","Green",
MAX('Car'[Brand])="Ferrari","Red",
MAX('Car'[Brand])="Rolls Royce","Blue",
MAX('Car'[Brand])="Porsche","Orange"
)

4.Select the fx icon under the Default color, then select the measure as the based on field.

vkalyjmsft_4-1638784736879.png

 

vkalyjmsft_5-1638784736880.png

 

5.For the table of brand, select conditional formatting and select the measure as the based on field also.

vkalyjmsft_6-1638784736881.png

 

6.Get the final result.

vkalyjmsft_7-1638784736892.png

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

Hello, thank you for your extended response. It looks like it can work but I have a question about recreating formula for the rank column if I also have month as a category (so I want to rank Market Share in month first, then in country by brand). And the next thing I'm concerned about is that method working for other visuals like bar chart and line chart?

Hi @mzienowicz99 ,

According to your description, you can modify the formula like this:

Rank =
RANKX (
    FILTER (
        'Car',
        'Car'[Brand] = EARLIER ( Car[Brand] )
            && 'Car'[Month] = EARLIER ( Car[Month] )
    ),
    'Car'[Market Share],
    ,
    DESC,
    DENSE
)

In this way, the rank of market share will be depending on the month.

vkalyjmsft_0-1639470596677.png

For bar and line chart, it also works fine. 

First, put country in axis, market share in values and rank in visual filter, then set rank is 1.

vkalyjmsft_1-1639470738120.png

Also click fx in Data colors, and use the color measure.

vkalyjmsft_2-1639470893603.png

vkalyjmsft_3-1639470969183.png

Get the expected result, and the month should be putted in a slicer.

vkalyjmsft_4-1639471014328.png

 

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

 

Anonymous
Not applicable

@mzienowicz99 yes, by mapping I mean connecting main table to this new table with Brands and their colours. As mentioned in your snap, I assume that is the colour table and your main table must have brand names' column to which you map this with. You can then use the colour column from new table to format the brands from main table. The relationship between the table can be single or bi-directional as per your filtering needs.


 

Did I answer your question?

Please mark the solution as Accepted!

Anonymous
Not applicable

@mzienowicz99 you can try creating a table with Brands and their colours and map that with your fact table. Use the colour column from this newly created table to conditionally format the visuals.

 

 


Did I answer your question?

Please mark the solution as Accepted!

OK, thanks, sounds good but my only issue is with mapping the new table with the table that is a base to my map visual? how to do it so the colour codes only appear next to right brands? because thats what you mean by mapping? or map the color table to the main table with whole data. Sorry, for my lack of knowledge but I'm a begginer and I am reallly curious about most things 😞

And also can i then format conditionally from a table looking like this, just from codes

mzienowicz99_0-1638462736083.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.