Forum Discussion
IF statement totaling issues
I'm attempting to use IF statements on a measure that calculates properly. With the IF statement measure, the individual line shows the correct amount but the totals do not foot.
To illustrate, here is the working formula:
sumx(' account_statement_item',
countrows(
filter(ALL_Dates,ALL_Dates[ALL_DATES]>=' account_statement_item'[date_start] && ALL_Dates[ALL_DATES]<=' account_statement_item'[mod_date_end]
&& related(' account'[type])<>"internal" && OR(related(' account_statement'[current_status])="paid", related(' account_statement'[current_status])="unpaid")))
*divide(' account_statement_item'[Line Net Revenue],iferror(datediff(' account_statement_item'[date_start],' account_statement_item'[mod_date_end],day)+1,0)))I have another iteration of this where it just moves the dates back a month in order to calculate the change. This also works fine.
VAR LastMonth = PARALLELPERIOD(ALL_Dates[ALL_DATES],-1,MONTH) RETURN
But... when I run this, it does not properly sum the total even though it appears to be calculating correctly on each row.
Upsells = if([Sales]-[Sales LastMonth]>0, [Sales]-[Sales LastMonth],0)
Attached is a screenshot with a red circle around the measure above and $0 total. The last column is a measure that simply takes [Sales] - [Sales LastMonth]. The end goal is to show the components of change - which lines had positive increases vs. which ones had decreases and put them in respective buckets.
10 Replies
Image
For troubleshooting, what happens if you change the last part of your upsells measure to 1 instead of 0? Do you get 1 in the totals, or is it still 0?
A net total shows up rather than only showing the total of individual lines > 0, but many rows have $1 on it (which is incorrect).
You need to wrap the measure you currently have in some sort of a SUMX, so it calculates each row and then sums the result instead of calculating the whole table at once.
I guess since the original formulas that the IF statement is calculating off are both wrapped in SUMX, what would you propose? I tried creating a much bigger formula by joining the two within an IF statement and had no luck.
