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.
We have items in inventory with expiration dates that we can use to find the remamining percent shelf life. I have a slicer that can adjust a threshold to identify items below a given shelf life %. I have a metric that calculates if an item is below the threshold:
Below Threshold =
var threshold = SELECTEDVALUE(Threshold[Parameter])
return
IF([Shelf Life] < threshold, "BELOW", "OK")
Data looks like this: (Assumming slicer Threshold = 40%)
[Column] | [Column] | [Metric] |
Item | Shelf Life | Below Threshold? |
Item 1 | 65% | OK |
Item 2 | 15% | BELOW |
Item 3 | 45% | OK |
Item 4 | 20% | BELOW |
Item 5 | 30% | BELOW |
(There are a few other categories in the live data, simplified here.) I would like to do some summary metrics based on the Below Threshold Metric (Count, also later sum of item inventory value, etc.). Like this:
Comment | Total Items |
OK | 2 |
BELOW | 3 |
I have a Comment table with the list of possible comments, but am struggling to find the right formula to get the Count calculation correct. Any help would be appreciated. Thank you.
Solved! Go to Solution.
Hi @StevenHiatt ,
First, create a table named Comment which contains OK and BELOW.
Then, create a measure use the following expression:
Measure =
var _s = SUMMARIZE('Table',[Item],"if below",[Below Threshold])
return
COUNTROWS(FILTER(_s,[if below]=SELECTEDVALUE(Comment[Comment])))
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @StevenHiatt ,
First, create a table named Comment which contains OK and BELOW.
Then, create a measure use the following expression:
Measure =
var _s = SUMMARIZE('Table',[Item],"if below",[Below Threshold])
return
COUNTROWS(FILTER(_s,[if below]=SELECTEDVALUE(Comment[Comment])))
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-chenwuz-msft, that worked exactly as I'd hoped. Looks like I need to learn more about SUMMARIZE and how it works. I appreciate your help.
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 |
---|---|
106 | |
104 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |