Clustered Column Chart Statically Set Colors

[hnguyen76]

Good morning, All.

I am using a clustered column chart where values are driven by a Quarter-Year slicer. My visual setup is as followed:


When a user selects a different quarter-year, I would like my colors to stay static. For example, using these three primary colors:

The current behavior is when I change a quarter-year, the colors are currently changing since each quarter-year has a color associated to it as such:


How do I force legend colors to be consistent for the visual? 

[Anonymous · Accepted solution]

@hnguyen76  - To clarify, is this correct?: Consistency in this case means that there will always be 3 columns: first column will be dark blue, second medium, and third light. 

The problem with this is that you want the color for a particular quarter to change depending on its position in the chart.

One way to accomplish this is by using static categories in the Legend.

1. Create a Date Table that contains relative quarters (like the Date table in the attached pbix).

2. Create a Parameter table with the list of quarters and their relative quarters from above date table.

Parameters = 
var a = SELECTCOLUMNS('Date',"Quarter Year", 'Date'[Quarter Year], "Relative Quarter",'Date'[Relative Quarter])
var b = DATATABLE(
    "Selected Quarter Description", STRING, 
    "Selected Quarter Order", INTEGER, 
    {{"Previous Year", 1},{"Previous Quarter", 2},{"Selected Quarter", 3}}
)
return CROSSJOIN(a,b)

3. On the Model Pane, set the Sort By value for the various Quarter values.

4. Create a relationship between your fact table and the new Date table.

5. Create a Measure which gets the value for the relevant quarters.

Value By Relevant Quarters = 
var _ThisQuarter = SELECTEDVALUE(Parameters[Relative Quarter])
var _LastQuarter = _ThisQuarter - 1
var _LastYearQuarter = _ThisQuarter - 4
return
SWITCH(
    MAX('Parameters'[Selected Quarter Order]),
    1, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastYearQuarter), 
    2, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastQuarter),
    3, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _ThisQuarter)
)

6. Create a slicer from the Parameters[Quarter Year].

7. Add a Column Chart, with Parameters[Select Quarter Description] in the Legend, and [Value by Relative Quarters] for the values.

Please see attached PBIX which demonstrates the logic.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[hnguyen76]

Hi @Anonymous 

That is actually correct. I just want the three columns to show with three colors. I've downloaded the PBIx and it looks like what I definitely need! Thanks for that!

Instead of displaying "Previous Year", "Previous Quarter", "Selected Quarter", could it be modified to show instead the values? For example, Previous Year would be "Q4-2020", Previous Quarter would be "Q3-2021", and "Selected Quarter" would be "Q4-2021"?

[Anonymous]

@hnguyen76  - I don't think there's a way to make that work, because each category (quarter) will be associated with a color. So, when the user interacts, those colors are still associated. That is why I created the 3 static values for the categories.