Forum Discussion
Helper IIIMin filtered value versus Max filtered value
Dear community, Another super easy question I believe from my side:
So, I have a (fake) report in which I filter two ingredients in order to compare them with each other value-wise.
E.g. I choose Milk A and choose Milk Z - now both ingredients have a price. I want to calculate the price deviation based on my selection. MIN minus MAX based on the selection would do the job, but like this my result value would always have the same prefix, which could cause confusion.
But what I am looking for is: Based on my selection it shall always calculate the first value (MIN based on a string [MILK A]) vs. the last value (MAX based on a string [MILK Z]). I don't want it to calcualte based on MIN or MAX price of the different ingredients but on the first vs. last selected value based on the string sort order. And there are a lot of filter value is, so I cannot directly filter based on MILK A or MILK Z - therefore the MIN/MAX idea....
Hope my point is clear, thanks a lot!!!
Hi I_LOVE_POWER_BI ,
To my knowledge, we are unable to get the order of the selected values in slicer.
I tried MIN() and MAX() for selections ,but it always be the minumum value and then the maximum that regardless of order:
In this case, if you do need to calculate the difference based on the selection order in slicer, I suggest you create a new table for the second slicer.
Second Slicer = VALUES('Table'[Ingredients ])Then use the following formula to create measure:
Measure 2 = MIN('Table'[Price])- MAXX(FILTER(ALL('Table'),'Table'[Ingredients ]=MAX('Second Slicer'[Ingredients ])),[Price])The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks guys for your responses. In between I found a very nice article about it, especially the update posted in the bottom of the article, which solved my issue:
3 Replies
- amitchandak
Super User
I_LOVE_POWER_BI , Based on what I got try a measure like
measure =
var _max = maxx(allselected(ingredients), ingredients[ingredients])
var _min = minx(allselected(ingredients), ingredients[ingredients])
return
calculate(Average(Table[price]), filter(ingredients, ingredients[ingredients] =_max)) -calculate(Average(Table[price]), filter(ingredients, ingredients[ingredients] =_min)) Hi I_LOVE_POWER_BI ,
To my knowledge, we are unable to get the order of the selected values in slicer.
I tried MIN() and MAX() for selections ,but it always be the minumum value and then the maximum that regardless of order:
In this case, if you do need to calculate the difference based on the selection order in slicer, I suggest you create a new table for the second slicer.
Second Slicer = VALUES('Table'[Ingredients ])Then use the following formula to create measure:
Measure 2 = MIN('Table'[Price])- MAXX(FILTER(ALL('Table'),'Table'[Ingredients ]=MAX('Second Slicer'[Ingredients ])),[Price])The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I_LOVE_POWER_BIThanks guys for your responses. In between I found a very nice article about it, especially the update posted in the bottom of the article, which solved my issue:
