Forum Discussion

hgnisnattart's avatar
hgnisnattart
Frequent Visitor
5 years ago
Solved

Apply Dynamic Threshold Filter to Measure

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
hgnisnattart
Frequent Visitor
5 years ago

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's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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]

    )