Recurring of Sequential colors in column chart

[user254]

I have a column chart. The dimension column added in this chart is having 200 fields (string fields like customer name). I want to assign 6 different colors for the fields visible on the chart based on the slicer changes. The colors am having are not solid colors. I have HEX codes for those colors (you can use any random 6 color codes to work on).  These 6 colors should get assigned to the columns in a sequential manner. For example, if only one column is visible in the chart then it should have the first color. IF there are 2 columns in the chart then the first column with first color and 2nd column with 2nd color. Similarly, if six columns are there then 6 columns with 6 different colors. if the visual is having a 7th column then it should get assigned with the first color and if it is having 8th column it should get assigned with 2nd color. similarly, 9th column with 3rd color and so on and after six columns the color sequence should start from first. So for every 6 columns(fields) the color sequence should be repeated in a same sequence upto 200 fields and even more (some times 1000 fields might be available). So if any new fields are added to the dimension the color should be assigned automatically.  I have done this in tableau using the below calculation "([Index]-1) % (6) + 1". See if it can help you in power bi. I have added the calculation to the color card and adjusted in stepped colors in tableau. If the above condition is acheivable in the clustered column chart in Power bi then see if it can be done in donut chart also.

[user254]

Hi @Anonymous ,
I have 2 issues with this approach.
1)You have created this approach with a small data. But the data I work is very large. So I use the Direct Query mode for the data. In direct query I can't create an Index column and other calculated columns as it takes alot of time to add columns. So Is there any other way to do it??
2) If I select any random values from the group slicer, the series of colors is not changing as per the sequence order. Irrespective of the values selected the color sequence should be same (like red, green, yellow,black, blue, orange) Below is the snippet for your reference.

 We can see that the 4th column is orange but it should be black as per the sequence.

[Anonymous]

Hi  @user254 ,

I created some data:

Here are the steps you can follow：

1. In Power Query -- add Column – Index Column – From 1.

2. Create calculated column.

Mod =
var _mod=MOD('Table'[Index],6)
return
IF(
    _mod=0,1,0)

Group_sum =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Index]>=EARLIER('Table'[Index])),[Mod])

rank =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Group_sum]=EARLIER('Table'[Group_sum])),'Table'[Index],,ASC)

3. Create measure.

color =
SWITCH(
    MAX('Table'[rank]),
    1,"red",
    2,"green",
    3,"yellow",
    4,"black",
    5,"blue",
    6,"orange")

4. Column – Color -- fx

5. Result:

Best Regards,

Liu Yang

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