Filtered Values vs Relevant Benchmark Data - FILTER based on a SELECTEDVALUE?

[AvionMarie]

Hello I wonder if anyone can help me with some ideas, I am stuck! The dataset I'm working with is road accident data in a part of the UK and I'm trying to compare statistics on one route with relevant comparable benchmark data. 

Once a route is selected (in the yellow filter box on the left) I would like the report to automatically display data for comparable roads on the right - the comparable data needs to change to reflect the Road Type the Select Route falls under (e.g rural, urban etc).

Question 1: How can I get this data to automatically filter the results by the associated Road Type on the right? At the moment the end user has to tick the box to show the relevant Road Type control data (by default it shows all road types so the figures are wrong)

I've tried to piece together a DAX expression to try and do a FILTER based on a SELECTEDVALUE but it didn't work!

Perhaps DAX isn't the solution? Any ideas?!

Also, I would like to create a KPI card that shows whether the selected route data is higher or lower than the relevant benchmark data. At the moment the data is in two separate cards side by side.

Question 2: How can I get this to work? (As with my first question, the control data benchmark needs to change depending on the road type it is)

[stretcharm]

Are the 2 data sets joined?

Do you have the road type benchmarks at road type level?

If  not maybe you can create a dax table at road type level. Then join the road to this new table using the roadtype.

You can use something like this to add a dax table. Goto Modelling and click table.

RoadTypeSummary = 
ADDCOLUMNS(
    SUMMARIZE(
        'RoadBenchmarks'
		,RoadBenchmarks[RoadType]
	)
	,"DataX",CALCULATE(COUNTROWS(DataX))	
	,"DataY",CALCULATE(SUM(RoadBenchmarks[DataY]))
	,"SDDataY",CALCULATE(STDEV.P(RoadBenchmarks[DataY]))
	)

You can then join to your road table.

It should filter with the road.

You can write dax from this on the road table or bring in the benchmark data to the road table using the RELATED function.

This makes it easy to write expressions that compare the current and benchmark values.

Also if you don't need it you can hide the summary table to keep the model a bit simpler.

[AvionMarie]

Thank you for your time and thoughts on this. 

I followed your advice, it worked well plus it inspired me also to try something else that worked well - I duplicated the table, linked the two identical tables together and used one table for one set of data and the other for the control data.

Thanks again

Marie

[stretcharm]

Great stuff. Glad to be of help.