Forum Discussion
Dynamically change tolerance value in PowerBI
I have some data that looks something like below:
| ID | Value |
| A | 2 |
| A | -1 |
| B | 6 |
| D | 0 |
Want I want to achieve is to create a new column Category based on the "Value" column, based on if-else rules.
E.g.
If "Value" < 0, "Category" = "Negative"
elif "Value" > 0, "Category" = "Positive"
else "Category = "OK"
I want to be able to add a slicer which defines the threshold for negative/positive. So when I set the slicer at 2, "Category" will only become "negative" when "Value" < -2 and "positive" when "Value" > 2
What I tried:
I created a table with values 0 - 5, which serves as the basis for my slicer (single select a value 0-5).
1. Create additional column in my table, one for each possible value in the slicer and create a measure that selects a column using SWITCH. Very ugly solution & gives me the error that multiple columns cannot be converted to a scalar value.
2. create a measure like below:
Hi Anonymous ,
Check the formula. You could create a slicer table and use selectedvalue('slicer'[value]) to replace the zero in the formula.
Measure = SWITCH(TRUE(),SELECTEDVALUE('Table'[Value])>0,"Positive",SELECTEDVALUE('Table'[Value])<0,"Negative",SELECTEDVALUE('Table'[Value])=0,"OK")There are multiple values for a single id, please check the "Don't summarize" option to avoid the aggregation.
You mentioned you want to create a Pie chart, but Pie chart doesn't support "Don't summarize". And Pie chart also doesn't support to put measures to the value field.
In addition, I don't know how you want to display the positive and negative values in Pie chart. So, if you want further help please share more details.
Best Regards,
Jay
3 Replies
- amitchandak
Super User
Anonymous , You have to create a measure like with help from a what if Param
Measure =
var _1 = Sum(Table[Value])
Switch( True(),
_1 < selectedvalues(Param[Param]) , "Negative"
_1 > selectedvalues(Param[Param]), "Positive"
"OK")
You need to have a table(bucket) with three rows
Negative
Positive
OK
Then you need a measure like
Sumx(filter(Values(Table[ID]), [Measure] = max(bucket[Value])) , [Measure])
refer
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kThanks amitchandak, one follow-up question though. I don't understand Sumx(filter(Values(Table[ID]), [Measure] = max(bucket[Value])) , [Measure]), what do you mean by bucket[Value]? The categories, but then MAX() function will not work. Or the value which the label belongs to, but then this won't by dynamic with my slicer right?
Hi Anonymous ,
Check the formula. You could create a slicer table and use selectedvalue('slicer'[value]) to replace the zero in the formula.
Measure = SWITCH(TRUE(),SELECTEDVALUE('Table'[Value])>0,"Positive",SELECTEDVALUE('Table'[Value])<0,"Negative",SELECTEDVALUE('Table'[Value])=0,"OK")There are multiple values for a single id, please check the "Don't summarize" option to avoid the aggregation.
You mentioned you want to create a Pie chart, but Pie chart doesn't support "Don't summarize". And Pie chart also doesn't support to put measures to the value field.
In addition, I don't know how you want to display the positive and negative values in Pie chart. So, if you want further help please share more details.
Best Regards,
Jay
