Measure totals not summing correctly

[ddbaker]

Hi everyone,

Here is my current measure. Basically what it's doing is checking to see if a customer has any future invoices and holds the revenue amount equal to the previous invoice amount. 

Monthly ARR = 
VAR LastInvoice =
    CALCULATE(
        MAX(Invoice[Invoice Date]),
        DATESBETWEEN(
            'Calendar'[Date],
            CALCULATE(
                MIN('Calendar'[Date]),
                ALL('Calendar')
            ),
            LASTDATE('Calendar'[Date])
        )
    )
VAR NextInvoice = 
    CALCULATE(
        MIN(Invoice[Invoice Date]),
        DATESBETWEEN(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            CALCULATE(
                MAX('Calendar'[Date]),
                ALL('Calendar')
            )
        )
    )
RETURN
IF(
    CALCULATE(
        SUM(Invoice[ARR Amount]), 
        Invoice[Months]=1
    ) = 0,
    IF(
        CALCULATE(MAX(Invoice[Months]),DATESBETWEEN('Calendar'[Date], LastInvoice, LastInvoice))<>1,
        0,
        IF(LastInvoice = 0 || NextInvoice = 0,
            0,
            CALCULATE(
                SUM(Invoice[ARR Amount]),
                DATESBETWEEN(
                    'Calendar'[Date],
                    LastInvoice,
                    LastInvoice
                )
            )
        )
    ),
    CALCULATE(
        SUM(Invoice[ARR Amount]), 
        Invoice[Months]=1
    )
)

 This measure actually works perfectly for each customer. But the total doesn't sum correctly the values in each row. I've been researching how to work with the total row context, but I haven't been able to figure it out. Any thoughts?

[Anonymous · Accepted solution]

I have not gone through your measure in detail, but if the measure returns the correct values at each customer level, but if only the total row is incorrect, then the easiest solution is to actually create another measure that adds the value of each customer using your current measure. Something like this..

New Measure =
VAR CustomerList = VALUES( < customer field> )
VAR Result = SUMX(CustomerList, Monthly ARR)
RETURN Result

Then, instead of the measure, "Monthly ARR", use this "New Measure" in your report visuals. What it does is the following....

1) Creates a temporary table with the list of customers (VALUES)

2) Iterate through this table and for each customer entry, it will calculate the "Monthly ARR" in the context of each customer, and then sums it up.

3) For each row, the VALUES is gonna return only one customer, but in the total, it will return all the customers in the current context. In either case, SUMX will give you the desired output.

Please try this out and see if it gives you the correct results. If not, do reply with more details.