topic Re: Summing an IF Statement in DAX Commands and Tips

Summing an IF Statement

Anonymous — Wed, 26 Apr 2023 14:12:56 GMT

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

Re: Summing an IF Statement

johnt75 — Wed, 26 Apr 2023 14:38:27 GMT

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

Re: Summing an IF Statement

Anonymous — Wed, 26 Apr 2023 15:33:52 GMT

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

Re: Summing an IF Statement

johnt75 — Wed, 26 Apr 2023 16:00:11 GMT

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

Re: Summing an IF Statement

Anonymous — Wed, 26 Apr 2023 17:40:33 GMT

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

Re: Summing an IF Statement

Anonymous — Sat, 11 Nov 2023 14:18:43 GMT

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