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 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!
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]

    )

5 Replies

    • 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]

        )