Apply Dynamic Threshold Filter to Measure

[hgnisnattart]

Hi, I'm trying to apply a dynamic threshold to an aggregated variance measure I created. 

In the image below, the graph on the left shows the variance (Var) by category. I want the graph to show the variance greater than or below the threshold specified by the slicer. For example, since 0.50 is the threshold selected, the only categories that should show up on the graph should be 'Finance' and 'Marketing' since the variance is either above 0.5 or under -0.5. 

I made a measure called Filtered Var with the code below (and hardcoded the threshold for now). However, this results in completely different values (shown in graph on right). 

Filtered Var = calculate([Var], filter(Details, [Var] <= -0.5 || [Var] >= 0.5))

How can I apply the threshold as a filter directly on [Var]? 

Thanks!

[amitchandak]

@hgnisnattart , Use a what if measure and use that in place of static value

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[hgnisnattart]

Hi @amitchandak, the threshold is a 'What-if' parameter. I put in the hardcoded value above for testing.

The Variance measure on the right graph is not matching the Variance measure on the left graph in the above image after applying the filter.

Below is how I want my graph to look, but the following measure is giving me a different variance than the image above:

Filtered Var = calculate([Var], filter(Details, [Var] <= -1*SELECTEDVALUE(Threshold[Threshold])|| [Var] >= SELECTEDVALUE(Threshold[Threshold])))

How can I apply the filter and get the same variance?

Thanks!

[Greg_Deckler · Accepted solution]

@hgnisnattart This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

In your case it is a measure filter problem but same concept. It's just you would have an extra filter in there like:

SUMX(

  FILTER(

    SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ),

    [Measure] > <some threshold>

  ),

  [Measure]

)

[hgnisnattart]

Thank you, @Greg_Deckler! This worked perfectly. 

[Greg_Deckler]

@hgnisnattart  😄