cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dgwoodard
Frequent Visitor

Counting instances of measure results

Hi everyone!

 

I have been working on a series of measures to determine how many weeks my inventory is expected to last when paired with a weekly sales forecast.

I have a starting or current inventory quantity and an estimated forecast by week.

I created a measure to calculate the running total of the forecast quantity:

 
ForecastQTYRunningTotal =
CALCULATE(
    SUM('ForecastTable'[Forecast Quantity]),
    FILTER(
        ALLSELECTED('ForecastTable'[Forecast Date]),
        ISONORAFTER('ForecastTable'[Forecast Date], MAX('ForecastTable'[Forecast Date]),DESC)
    )
)
 

then another to invert the running total:

ForecastInv = [ForecastQTYRunningTotal]*-1


and then another measure to calculate the sum of the forecast against the current inventory quantity over each week listed in the forecast (realizing I could probably combine these two into one but trying to piece this all together step by step):

 
InvRemaining = sum('InventoryTable'[inventory])+[ForecastInv]

I then created yet another measure to indicate whether the clauclation by the previous measure is > than 0 with:

Is_InvRemaining_+ =
var WksOnHnd = IF([InvRemaining]>0,"yes","no")
return WksOnHnd

The results of these four measures produce a table that looks like the following:

SKU#Forecast DateSum of Forecast QuantityForecastQTYRunningTotalForecastInvInvRemainingIs_InvRemaining_+
361/18/202124942494-249412985yes
361/25/202124524946-494610533yes
362/1/202127987744-77447735yes
362/8/2021292010664-106644815yes
362/15/2021236413028-130282451yes
362/22/2021222815256-15256223yes
363/1/2021238617642-17642-2163no
363/8/2021214019782-19782-4303no
363/15/2021209021872-21872-6393no
363/22/2021212023992-23992-8513no
363/29/2021286826860-26860-11381no
364/5/2021273429594-29594-14115no

 

My question is how do I count the number of instances which return a "yes" to indicate how many weeks my current inventory will last? I have also tried this by returning a 1 or 0 with the intention to calculate the sum but have run into roadblocks with both the SUM function and the COUNT function as they only apply to columns (?). 

My hope is to be able to populate a card that would indicate the number of "yes" or "1" instances as a total number of weeks

Thanks in advance!
1 ACCEPTED SOLUTION

@dgwoodard , Try like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return 

countx(_tab, if([_1]>0,1,blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
dgwoodard
Frequent Visitor

Also worth noting that I have a slicer filtering the data table and measures by SKU # in order to isolate the detail of each product.

 

@dgwoodard , Try a measure like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return 

countx(_tab, if(_tab[_1]>0,1,blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I feel like this is really close to success but getting an error of "Cannot find table '_tab'."

dgwoodard_0-1610504500105.png

 

@dgwoodard , Try like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return 

countx(_tab, if([_1]>0,1,blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Sucess! - thank you.

 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.