Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |