Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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 , Use a what if measure and use that in place of static value
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
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:
How can I apply the filter and get the same variance?
Thanks!
@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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |