Forum Discussion
Summing an IF Statement
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 ) )
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
Super UserThe 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 )
)
johnt75 Yayyy! It worked!!! 😀 Thank you so so much!!!!
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