Skip to main content
cancel
Showing results for 
Search instead 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

Reply
hnguyen76
Resolver II
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:
visual_setup.png
filter_selection.png
When a user selects a different quarter-year, I would like my colors to stay static. For example, using these three primary colors:
static_example.png
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:
static_example_01.pngstatic_example_02.png

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.

View solution in original post

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors