Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
}
)
)
)
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"
}
)
)
)
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 |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |