Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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())

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())

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())

Sucess! - thank you.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.