Comparison to Selection Query

[SESLewis]

Hi,

New to Power BI and DAX so please bear with me - For context and to simplify, I have a selection of products of which, we apply a performance 'score' to the material they are made from.

I have been tasked to create a report that when a particular product is selected, it's material perfomance 'score' is compared to the other products in the list.

To clarify using the example below:

Using a visual like a bar chart it would be great to see the variance of the other products material performance to the product selected. So if 'Product 1' was selected the variances would be as follows:

If I selected 'Product 2' the variances would change accordingly.

This would be so powerful for some reporting I am doing for a number of reasons but no matter what I try I cannot seem to get this to work. I have managed to create a 'hack' version where I have created a SELECTVALUE measure and then subtracted the particular perfomance score from this, which looks a little like this:

Product1Variance = CALCULATE([material perfomance],Product Table [Product] = "Product1") - [Select Value]

'Select Value' Looks like this:

Select Value =

 VAR MaterialSelect = SELECTEDVALUE(Product Table [Product])

 RETURN

 CALCULATE([material performance])

The problem with this, which you may have guessed already, is that I will eventually have hundreds of products and creating a measure for each product is counter productive and makes any visuals look janky and unreadable. 

I don't even know if using a SELECTVALUE approach is even the right path in the first place.

I have looked all over the place and found lots of comparison style questions and answers but most of them revolve around comparisons to prior years/months etc - I feel like this should be fairly simple but I am thinking that as I am new to alot of this that I am missing something obvious!

Any help would be greatly appreciated!

[Grey343]

Is there any way to do something like this, but with a KPI? Where [Material Performance Measure] is the callout value, and [Var to Product] is the target? 

[SESLewis]

Many thanks Aniya! This has helped me alot and I have managed to apply this to what I have been working on. I hope this helps others where comparisons are needed without date ranges.

Regards

[v-yueyunzh-msft · Accepted solution]

Hi , @SESLewis 

According to your description, you want to "Comparison to Selection Query".
Now, i have found no way to update the measures' name according to the filter selection.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to click "New Table" to create a calculated table as a slicer and we do not need to create any relationship between two tables:

Table 2 = VALUES('Table'[Product])

(3)I create a measre to test :

Material Performance Measure = SUM('Table'[Material Performance])

(4)Then we can create  a measure like this:

Var to Product = var _select = MAX('Table 2'[Product])
var _t =FILTER( ADDCOLUMNS( ALLSELECTED('Table'[Product]) , "value" , [Material Performance Measure]) , [Product] = _select)
return
[Material Performance Measure]-SUMX(_t ,[value])

Then we can put the fields we need on the visual adn we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly