IF statement totaling issues

[avalerion]

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.

[avalerion]

Image

[jahida]

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.

[avalerion]

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.

[jahida]

If I understand correctly (I may not), the objective here is to calculate at the row level in the table visual (being done correctly) and then sum those results to get a final total. If that's the case, then you need a SUMX which groups together each row of the table visual, calculates for each, and then sums. SUMMARIZE might be useful:

SUMX(SUMMARIZE(Table, Table[Col1], Table[Col2], "Value", IF(...)), [Value])

Where Col1 and Col2 are the columns of the table that represent rows in the visual.

[avalerion]

The big issue is that the values needing analysis aren't stored in any table - they're measures only since there are a few rules applied in order to figure out what the true sales for a given month are.

[jahida]

What is the setup of the table? Like what defines a row? Is it a month, a product, both, etc.

[avalerion]

There are four tables:

1- customer master

2- invoice header

3- invoice detail (including the dates of service)

4- master date table

Each invoice has 1 row in the header, and multiple in the detail. In order to figure out when the service was provided, running a SUMX on the detail table with a calculation on the number of days between MIN_Date & MAX_Date is necessary. The service dates in detail are tied to the master date table. So the only thing that can be pulled from the table are the start/end dates and line item amount.

A row can be either the customer account to see details OR a specific month in order to compare monthly trends.

Relationships are setup from: customer master >> invoice header >> invoice detail

[jahida]

Ok can you try something like

SUMX(SUMMARIZE(ALL_Dates, ALL_Dates[Month], ALL_Dates[Year], "Value", IF(...)), [Value])?

[KGrice]

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?

[avalerion]

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