Summing an IF Statement

[Anonymous]

Hi Community! 

I am looking to create an accurate Sum total column (see highlighted totals below):

This Gaps closed measure is: 

Gaps Closed = 
IF([Total Qty Baseline]= 0,
1,
0)

Count of Reorders measure is:

Count of Reorders = [New Distriubtion Count AMJ] - [Gaps Closed]

I feel like I'm overthinking this, but is there a simple solution to have a Sum total column when using an IF statement?

Thank you! 

Christina

[johnt75]

You need to iterate over the table so the calculation is done row by row, e.g.

Gaps Closed =
SUMX ( 'Table', IF ( [Total Qty Baseline] = 0, 1, 0 ) )

[Anonymous]

Hi @johnt75!

Thanks for the suggestion.  Still not summing, but I'm guessing it's because I'm using a measure as my table reference.

My total baseline measurement is including a date range.  So between these two dates, sum the qty of units sold.

Total Qty Baseline = CALCULATE(SUM('DOCUMENTS'[Quantity]),DATESBETWEEN('DOCUMENTS'[Posting Date], DATE(2022,10,01),DATE(2023,04,07)))+0

New Distribution Qty AMJ: 

Total Qty AMJ = CALCULATE(SUM('DOCUMENTS'[Quantity]),DATESBETWEEN('DOCUMENTS'[Posting Date], DATE(2023,04,08),DATE(2023,06,30)))+0

If Baseline qty was zero, then calculate sum AMJ qty 

New Distribution Qty AMJ = 
IF([Total Qty Baseline] = 0,
[Total Qty AMJ],
0)

Now I'm trying to show - if baseline qty was zero, then show "1" (meaning new distribution).  

Even trying to use the table "Documents" within the sumx formula, would not allow for 1 to show.  Instead it would count the documents.

Thanks again,

Christina

[johnt75 · Accepted solution]

The table you use to iterate over in the SUMX needs to match the values you are showing in the visual, so it needs enough columns to uniquely identify each row in the visual. If you have a fact table holding sales, and dimension tables holding other columns you could do something like

Gaps Closed =
SUMX (
    SUMMARIZE (
        'Sales',
        'Territory'[Territory Code],
        'Customer'[Customer number],
        'Items'[Item number]
    ),
    IF ( [Total Qty Baseline] = 0, 1, 0 )
)

[Anonymous]

@johnt75  Yayyy!  It worked!!! 😀 Thank you so so much!!!! 

[Anonymous]

Hi @johnt75!

I am looking to add onto this already created dax function you graciously helped me with.  I am looking to add the months as well onto this table, but having it only count for the first order of the date time frame (instead of each month).

This is the formula you helped me with shows (correct result):

But then when I add in months to the table- this is the result I get:

Any ideas on how I could add in a date filter so the formula is only calcuating the first posting of all months in the dataset?

ex. if customer ordered in August and September, - only count August Order.

Thanks so much,

Christina