Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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"
}
)
)
)
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.
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.
Hi @FinanceKG
whatp is the entityid? From which table? Is it affected by any of these filters?
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.
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?
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |