Calculate Totals of Items based on Metric Variable

[StevenHiatt]

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.

[v-chenwuz-msft · Accepted 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.

[StevenHiatt]

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.