cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Clustered Column Chart Statically Set Colors

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.
3 REPLIES 3
Anonymous
Not applicable

@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.
Resolver II

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
Not applicable

@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.