Sum of most recent values multiple filters

[FinanceKG]

Hello, I have the below measure which seems to return the correct result when looking at individual rows in a matrix, but it sums to 0. Does anyone have suggestions on how to write this so that the total also displays properly?

Pipeline Engineering Recent = CALCULATE(-SUMX(Filter('transaction','transaction'[createddate] = MAX('transaction'[createddate])),MAX('transactionaccountingline'[amount])),

Filter(Account,Account[accttype]="Income"),

Filter('transaction','transaction'[type] = "Estimate"),

Filter('transaction','transaction'[status] <> "C"),

Filter(CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE,CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE[name] in {"Ready for Engineer Vetting", "Engineering Requires Follow-Up", "Ready for Engineering", "Engineer Assigned", "Engineering in Process", "Engineering Waiting on Division", "Engineering Complete", "Ready for Quote", "Ready for Sales Order"}))

Currently displays in a matrix as below. Individual entries are correct.

[Mrxiang]

This measure appears to be calculating the total of all rows where the values in columns "transaction" and "transactionaccountingline" match a specific condition. The problem is that the "transaction" and "transactionaccountingline" columns do not have a "sum" function applied to them, so the values in those columns will not be summed when the calculation is performed.
To fix this, you can add the SUM function to the column references in the measure. For example, you could change this:

Pipeline Engineering Recent = CALCULATE(-SUMX(Filter('transaction','transaction'[createddate] = MAX('transaction'[createddate])),MAX('transactionaccountingline'[amount])),
Filter(Account,Account[accttype]="Income"),
Filter('transaction','transaction'[type] = "Estimate"),
Filter('transaction','transaction'[status] <> "C"),
Filter(CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE,CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE[name] in {"Ready for Engineer Vetting", "Engineering Requires Follow-Up", "Ready for Engineering", "Engineer Assigned", "Engineering in Process", "Engineering Waiting on Division", "Engineering Complete", "Ready for Quote", "Ready for Sales Order"}))

to this:
Pipeline Engineering Recent = CALCULATE(-SUMX(Filter('transaction','transaction'[createddate] = MAX('transaction'[createddate])),MAX('transactionaccountingline'[amount])),
Filter(Account,Account[accttype]="Income"),
Filter('transaction','transaction'[type] = "Estimate"),
Filter('transaction','transaction'[status] <> "C"),
Filter(CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE,CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE[name] in {"Ready for Engineer Vetting", "Engineering Requires Follow-Up", "Ready for Engineering", "Engineer Assigned", "Engineering in Process", "Engineering Waiting on Division", "Engineering Complete", "Ready for Quote", "Ready for Sales Order"}),SUM(Transactionaccountingline))

This will sum the values in the "transactionaccountingline" column for each row that matches the specified conditions.

[tamerj1]

Hi @FinanceKG 

whatp is the entityid? From which table? Is it affected by any of these filters?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

Pipeline Engineering Recent =
SUMX (
    VALUES ( entityidtable[entityid] ),
    CALCULATE (
        - SUMX (
            FILTER (
                'transaction',
                'transaction'[createddate] = MAX ( 'transaction'[createddate] )
            ),
            MAX ( 'transactionaccountingline'[amount] )
        ),
        FILTER ( Account, Account[accttype] = "Income" ),
        FILTER ( 'transaction', 'transaction'[type] = "Estimate" ),
        FILTER ( 'transaction', 'transaction'[status] <> "C" ),
        FILTER (
            CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE,
            CUSTOMLISTCUSTOMLIST_CG_JOB_PHASE[name]
                IN {
                "Ready for Engineer Vetting",
                "Engineering Requires Follow-Up",
                "Ready for Engineering",
                "Engineer Assigned",
                "Engineering in Process",
                "Engineering Waiting on Division",
                "Engineering Complete",
                "Ready for Quote",
                "Ready for Sales Order"
            }
        )
    )
)

[FinanceKG]

Thank you. This solved my first issue. I am trying to sum only the most recent amounts based on transaction created date. It is summing only one transaction, but it is not always the most recent one. Any ideas?