Changing Color’s of Bar charts

[Anonymous]

Hi Team, 
I have created a bar chart where I showing revenues by different account . At a time I am showing more than 20 bars . 
now I have to change Color's for each bars , I know there is a option where it will show the Color's for each bars and you can change from it there , but I am looking for a better solution where power bi automatically changes the colour of each bar on its own . Because if I change the year I am getting more than 100 bars and now I have to change the Color of the bar 1 by 1 to represent it .

Thanks & Regards

Shiv kumar

[Shishir22]

Hello @Anonymous ,

If you have large numbers of account then I will suggest to create a table as DimAccount with distinct account name and create a calculated column for Random Hex Code for all the accounts using below column as-

RandomColor =
VAR cR =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR cG =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR cB =
    RANDBETWEEN ( [Value] - [Value], 255 )
VAR RedP0 =
    MOD ( cR, 16 )
VAR RedP1 =
    MOD ( INT ( cR / 16 ), 16 )
VAR GreenP0 =
    MOD ( cG, 16 )
VAR GreenP1 =
    MOD ( INT ( cG / 16 ), 16 )
VAR BlueP0 =
    MOD ( cB, 16 )
VAR BlueP1 =
    MOD ( INT ( cB / 16 ), 16 )
VAR hexTable =
    ADDCOLUMNS (
        { RedP1, RedP0, GreenP1, GreenP0, BlueP1, BlueP0 },
        "Hex", SWITCH (
            [Value],
            10, "A",
            11, "B",
            12, "C",
            13, "D",
            14, "E",
            15, "F",
            [Value]
        )
    )
RETURN
    "#" & CONCATENATEX ( hexTable, [Hex], "" )

Create below for conditional formatting and use this as Color by values

MeasureForConditionalFormatting= Firstnonblank(DimAccount[RandomColor],0)

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

Please mark it as solution if it solves your issue. Kudos are also appreciated.

[Anonymous]

Hi @Shishir22 ,
Thanks for replying!!
If I am understanding it correctly the above formula , I still have to assign colors to each value .
Thanks & Regards
Shiv Kumar

[Shishir22 · Accepted solution]

No.

Follow below steps-

Step 1 - Create a table having unique account no/account name as DimAccount and add below calculated column in it. Give relationship to your fact table  on basis on account no/account name.

RandomColor = 
VAR cR =
    RANDBETWEEN ( 0, 255 )
VAR cG =
    RANDBETWEEN ( 0, 255 )
VAR cB =
    RANDBETWEEN ( 0, 255 )
VAR RedP0 =
    MOD ( cR, 16 )
VAR RedP1 =
    MOD ( INT ( cR / 16 ), 16 )
VAR GreenP0 =
    MOD ( cG, 16 )
VAR GreenP1 =
    MOD ( INT ( cG / 16 ), 16 )
VAR BlueP0 =
    MOD ( cB, 16 )
VAR BlueP1 =
    MOD ( INT ( cB / 16 ), 16 )
VAR hexTable =
    ADDCOLUMNS (
        { RedP1, RedP0, GreenP1, GreenP0, BlueP1, BlueP0 },
        "Hex", SWITCH (
            [Value],
            10, "A",
            11, "B",
            12, "C",
            13, "D",
            14, "E",
            15, "F",
            [Value]
        )
    )
RETURN
    "#" & CONCATENATEX ( hexTable, [Hex], "" )

Step 2- Create a calculated column in your fact table as 

Color = RELATED(DimAccount[RandomColor])

Step 3- Create a measure as 

FormatColor = FIRSTNONBLANK(Facttable[Color],0)

Step 4- Click on bar chart. In conditional formatting, click on fx.

Step 5- Select Format style to value and select format color measure.

You should get random colors for all bars.

Please mark it as answer if it resolves your issue. Kudos are also appreciated.

[Anonymous]

This works! Thank you so much, Shishir. Could you also please let me know how to make the color values not change every time I refresh the report? Like in Excel we paste the column as values or in some programming languages we set seed to control the randomness of values generated.

[mikedoyle]

How do I give this answer a thumbs down?  It's basically hard-coded.....

[amitchandak]

@Anonymous , if you are using one measure with no legend. You can do that.

You can create measure that return color based on account

Example

Switch(Max(Table[Account]) ,

"Account1" , "Red",

"Account2" , "Blue",

// Add others.

"Green" //Default

)

Use in conditional formatting, using the field value option

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539

[Anonymous]

Hi @amitchandak , 

Thanks for replying!!,

even if i create a measure for every company name i have to assign a value , If i filter the data for current year there are only 20 account names hence it is very easy to assign colors to those 20 different values , but when i switch to 2021 then it shows more than 100 , I want Power Bi to automatically assign colors for each value whatever it thinks of or a formula which will assign any color to all different bar charts.
Thanks & Regards
Shiv Kumar