Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Babette
Helper III
Helper III

Cluster bar to have same bar colors whether its 4 Months or 4 Fiscal Years

Hi,  I have a Cluster Bar visual that displays either Fiscal Year selected + 3 previous or MonthYear selected + 3 previous.

Bar colors should be #377DC4,#4396EA,#7AB2FB,#B4CFFC in each cluster only.  

When FY 18-19 is selected (right pic), it has the correct color and it is because I modified the Format > Data colors. 

But if I were to choose FY 15-16 or any Months, then i will not get the colors I want (left pic).

I tried importing new json theme {"name":"Equip","dataColors":["#377DC4", "#4396EA","#7AB2FB","#B4CFFC"],"tableAccent":"#8BC7F7","maximum":"#8BC7F7","center":"#0B31A5","minimum":"#D2D3D5"}

but it won't work and I am still getting different colors.

 

How can I control the cluster bar visual so it will always and only show the same 4 color bars (whether its FY or Month selected)?  Thank you for your help.

 

Cluster Bar Colors.png

6 REPLIES 6
nandic
Memorable Member
Memorable Member

@Babette ,
Add new column in table which will have desired color result.
In my example, i want to make sure that i have always the same color per year.

I added new column Year Color = SWITCH('Date'[Year],2015,"#377DC4",2016,"#4396EA",2017,"#7AB2FB",2018,"#B4CFFC",2020,"Blue")

Then in chart go to data colors > Fx > format by value > based on field Year Color > summarization First.

This way, no matter which year is selected, colors per year do not change.

Hello @nandic

 

Thank you for your suggestion.   The same cluster bar chart can display FY or Month (depending on what is selected in the slicer).  The FY and Month in the slicer is data driven.  I need to make the calculation dynamic and i am thinking if it is possible to ..

1 - Determine if FY or Month selected in slicer

2 - Get the Maximum or Minimum of the 4 FY or 4 Month of cluster bars shown

3 - Make the Minimum FY or Month bar have 'ColorBlue1'

4 - Add 1 FY or 1 Month to the Minimum so the next bar have 'ColorBlue2' and so on until we have 'ColorBlue4' 

If possible, could you show me how i can do so?  or are there alternate solution?

Much appreciated.

 

@Babette ,
At the moment i am not sure what is the best way to have these colors dynamic per selected year.
As this property is based on calculated columns (not measure) and slicers can not be passed to calculated columns.

Alternative is to use color scale instead of field. Use average year and set color for min and max value. It will not be exact color match as demanded, but it can be plan B.
Use the same logic for months (make sure month is in format yyyymm for multi year combination).

Here is screenshot (result chart on the left).

20200909 color scale.PNG

@nandic

hello,  I am so terribly sorry, i made a mistake - its Clustered Column Chart that i have.  There is no option in Data colors to use color scale.  Any suggestion what i can do for Clustered Column Chart.  Thank you so much.

 

@Babette , clustered column chart has option for color scale on data, but you need to make sure year / month is placed on x axis, not on legend.

 

clustered column chart conditional.PNG

@nandic 

If FY is selected, the Cluster Column chart will show 4 fiscal years of each Equipment selected (left pic).  If Month is selected, the chart will show 4 Months of each Equipment selected (right pic).   Icey helped to provide this solution as i did not know how i can report to show FY or Month in the Cluster Column chart.  

The X-Axis represents the Equipment and its a hierarchy, users can click on to drill into next level of equipments.

I don't think i can place the FY /Months on the axis.  Any suggestion please.  Thank you.

Cluster Bar Colors 1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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