SUM all records but use different fields depending on condition?

[macfuller]

I'm wondering if there's a simpler alternative to combining two CALCULATE statements.

In our Accounts Payable file there are two ways of getting the paid amount. (Hey, I didn't design the thing.) Under most circumstances we can do SUMX(Vouchers, [Unit Price] * [Quantity]). However, certain categories such as refunds don't have a unit price or quantity and they're entered into a different [Amount] field.

I can do two separate measures along the CALCULATE(SUMX(Vouchers, [Unit Price] * [Quantity]), FILTER(Vouchers, [Quantity >0)) and [Quantity]=0, (there are other filters, just doing this for simplicity) but I wondered if there's a way to put the IF statement inside the first parameter so I can tell which method I want to use to get the proper amount and leave the other conditions for the FILTER clause.

 

[Matt Allington]

Do you need an IF statement? Sounds like one or the other will always return 0, so why not just add them together.

 

[macfuller]

Yes, I've added the separate ones together. It just doesn't seem very elegant. I wondered if I could do a VAR, something like
VAR
SumField = IF([Quantity]=0, [Amount], [Unit Price]*[Quantity])
RETURN
CALCULATE (SUM(SumField), FILTER(etc.))

But I can't get that to work.

 

[Cool blue]

I reckon you're over-thinking it, why not something like this...

CALCULATE (
    SUMX(Vouchers,
        IF([Quantity]=0, [Amount], [Unit Price]*[Quantity])
    ),
    FILTER(etc.)
)

 

[macfuller]

You're right, I was overthinking. But, although the measure works in the model window, when I try to insert it into a pivot table I run out of memory and Excel tells me to try the 64-bit version of Office (which I'm running).

Combined Alt Voucher Total excl Interunit:=CALCULATE (
    SUMX(Vouchers, IF(Vouchers[Quantity] = 0, Vouchers[Voucher Amount], Vouchers[Unit Price] * Vouchers[Quantity])),
       FILTER (
        Vouchers,
            SEARCH("Interunit",
                Vouchers[Item Description], ,0) = 0
        ),
    USERELATIONSHIP ( Vouchers[Voucher Entered Date], Calendar[Date] ),
    USERELATIONSHIP ( Vouchers[Account], 'Account Tree'[Account] ),
    USERELATIONSHIP ( Vouchers[Dept ID], Depts[Dept] )
)

I'm not enough of a DAX guru to know why it pegged the memory and CPU but I guess I'll have to continue using the combined approach which returns the same number but without the drama. I suspect the SEARCH portion of the filter is the main culprit but I'm not sure how else to find and exclude Interunit transfers.

(The Calendar, Account, and Dept lookup tables have the default active relationship to purchase orders, which in turn relate 1:many to the vouchers. But there are non-PO expenditures so I need to re-route the relationships to the voucher table directly for some measures.)

 

[Cool blue]

I guess it's not material to your memory problem but, Matt is spot on about the if statement.

For the sake of learning though, try it without fully qualified field names...

Combined Alt Voucher Total excl Interunit:=CALCULATE (
    SUMX(Vouchers, IF([Quantity] = 0, [Voucher Amount], [Unit Price] * [Quantity])),
       FILTER (
        Vouchers,
            SEARCH("Interunit",
                [Item Description], ,0) = 0
        ),
    USERELATIONSHIP ( Vouchers[Voucher Entered Date], Calendar[Date] ),
    USERELATIONSHIP ( Vouchers[Account], 'Account Tree'[Account] ),
    USERELATIONSHIP ( Vouchers[Dept ID], Depts[Dept] )
)

Also, do you need the USERELATIONSHIP statements? Unless these are not set to "Active" in the Manage Relationships dialog, you don't need this.

 

[macfuller]

Voucher Amount always has a value (not necessarily the same as price * quantity) so I would be double-counting without the IF statement. And the relationships are not active - the primary relationship goes through the purchase order table, but since we can have non-PO expenditures the primary relationship would group them as <blank> in the slicers instead of giving me specific account and department info.