Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FinanceKG
New Member

Sum of most recent values multiple filters

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.
 
FinanceKG_1-1684879151913.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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"
            }
        )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Mrxiang
Helper II
Helper II

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
Super User
Super User

Hi @FinanceKG 

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

Jihwan_Kim
Super User
Super User

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"
            }
        )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors