Changing colour for multiple series based on filter selection in clustered bar chart

[lgoggs]

Hi, I've been playing around with this and wondered if it can be done. 

I have a category column called 'activity', which is the y-axis on a clustered bar chart. I then have 3 series called 'current_year',  'region_ha' and 'na' that I would like to add to the x-axis. Ideally I want to be able to colour each activity different for each series for each filter selection. So, the region 'Sheffield' when selected in the filter would have a different colour for the activity 'Running' to 'Walking', and also the colours would be different for 'current_year' compared to 'region_ha' and also different for each of these for 'Liverpool'. 

I did think it could be done with conidtional formatting, but you only get this option when you have 1 series added for the x-axis. I think the solution may lie in creating some kind of calculated measure with various IF statements, but I'm struggling to get my head around how this would be set up and structured. 

I've attemped to attach an example file (Opens in a new window) with some data below if anyone is able to take a look and any help would be greatly appreciated:

Example Report

Best wishes,

Luke

[MFelix]

Hi @lgoggs ,

Do you want to show all the calculations (current_year, na and region_ha) alonside with activity and location or do you want only to show one of the calculations and then the vcalues of activity and location?

How is the setup you need? Is the same you have on the example you gave?

[lgoggs]

Hi Miguel, 

Thank you so much for having a look at this and getting in touch. Ideally I would like to show all 3 calculations (current_year, na and region_ha) alongside each activity, but only for 1 location at a time (Set and selected by the filter). 

I did have all 3 calculations added to the x-axis but it then removed the ability to conditionally format the colours for each region and calculation. What I was hoping for (but not sure if it's possible), would be to have it show for say 'Sheffield', then have a different colour for each activity for current_year, different colours again for each activity for na calculations and the same again for region_ha. Then all the colours would be different again when a different region is selected. 

Hopefully I've explained that well, but not sure I have! Please do let me know if anything is unclear at all or if you have any further questions at all. 

Best wishes,

Luke 

[MFelix · Accepted solution]

Hi @lgoggs ,

For this you need to create a table with the Activity and Metric:

Now add the folllowing measures:

Values = SWITCH(
			SELECTEDVALUE('Activity / Metric'[Metric]),
			"current_year", CALCULATE(
				SUM(activity[current_year]),
				activity[activity] = SELECTEDVALUE('Activity / Metric'[Activity])
			),
			"na", CALCULATE(
				SUM(activity[na]),
				activity[activity] = SELECTEDVALUE('Activity / Metric'[Activity])
			),
			"region_ha", CALCULATE(
				SUM(activity[region_ha]),
				activity[activity] = SELECTEDVALUE('Activity / Metric'[Activity])
			)
		)




Colors = SWITCH(

			SELECTEDVALUE(activity[region]),
			"Liverpool", SWITCH(

				SELECTEDVALUE('Activity / Metric'[Metric]),
				"current_year", "blue",
				"na", "Green",
				"region_ha", "Yellow"
			),
			"Sheffield", SWITCH(

				SELECTEDVALUE('Activity / Metric'[Metric]),
				"current_year", "pink",
				"na", "orange",
				"region_ha", "Grey"
			)
		)

Now create your visualization in the following way:

• Y-Axis:
  • Actvity/Metric[Activity]
  • Actvity/Metric[Metric]
• X-Axis
  • Values (Measure)
• Condittional formatting
  • Colors (Measure)

You can change the colors by HEX codes if you want to.

PBIX file attach.

[lgoggs]

Oh my goodness Miguel, this is incredible!!! Thank you so much for your help with this and making it's possible. I cannot tell you how many hours I've spent trying to figure that out! 😅

A massive thank you. I appreciate it's not straight forward, so very grateful for your time and support. 

All the best, 

Luke