How Can you Total a if statement in a measurement

[Bmejia]

I have created a simple if measure, but I need it to add the total.  I can seem to find a solution, is this possible.  

AVPhy = if([Available Physical]=0 ||[Available Physical]=BLANK(),0,1)

My total becomes one

[Bmejia]

Hoping somebody can assist on how to count numeric values in an if statement measurement.  I have tried several ways and had no luck

first my available formula provides the total of purchase minus sold(Sold negative value reason for +) and it carries over any unsold to the following year.

If I have an item Available(In Stock) I want count it just as one regardless  if they have more than one.  I am able to get in the results below using the if statement, but it does not count the volume.  I want to be able to pull all the items in a year where I got a 1 and total it by year.

Available= CALCULATE(

    [Purchased]+[Sold],

        FILTER(

            ALL('Date'[Date]),

            ISONORAFTER('Date'[Date],MAX('Date'[Date]),DESC)))

If Statement

AVPhy = If([Available Physical]=0 || [Available Physical]=BLANK(),0,1)


One Item (Of Many)

FYYear	Purchased	In Stock Previous Year + Purchased	Sold	Available	AVPHY
FY18	20		14	6	1
FY19	12	18	16	2	1
FY20	17	19	17	2	1
FY21	0	2	0	2	1
FY22	0	-2	2	0

Total of all Items

FYYear	AVPHY  Total Per Year
FY18	251
FY19	254
FY20	456
FY21	456
FY22	245

[v-luwang-msft]

Hi @Bmejia ,

Pls change measure to the below:

AVPhy = SUMX('Table', if([Available Physical1]=0 ||[Available Physical1]=BLANK(),0,1))

outputresult:

Before（with not total value)：

Best Regards

Lucien

[Bmejia]

Thanks for feedback, unfortunately it did not work for me I have attached the link with my pbi file sample ( am not able to upload attachment).

So my “if statement” is looking to see if there is a value that is not zero or blank in the “available physical” column measure and it gives me a 1 if it does.  I tried what you providedit is adding each line with a number and not really excluding the 0 or blank from each year.

As a note the “Available Physical” is carrying over.  It is the volume of part that we have left inventory at the end of each year.

Any other feedback will be greatly appreciated.

Thanks again,

https://1drv.ms/u/s!Ah2n-pbAcSl_kC5f7deyWoheuWyZ?e=ANARbX