Forum Discussion
Incorrect total
Sorry, I cannot share the pbi but the problem is solved. The data model has some problem and i have adjusted it and added a colunm to table to help the calculation. The dax is the same as above. Thanks again.
katiehui Hi! The issue is that in your SUMX(DIM_CONTRACT_HEADER, ...), when DAX computes the total, it iterates over all contracts, not per row, and applies the TREATAS filter to all contracts at once, leading to inflated or incorrect results.
Try with:
expected_revenue =
SUMX (
VALUES ( DIM_CONTRACT_HEADER[CONTRACT_NUMBER] ),
VAR selected_dates = VALUES ( DIM_CALENDAR[YEAR_MONTHNAME] )
VAR contract = DIM_CONTRACT_HEADER[CONTRACT_NUMBER]
VAR amount =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[PRINCIPALAMOUNT] ),
DIM_CONTRACT_LINE[ITEM_ID] IN { "ID_1", "ID_2" },
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
VAR charge =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[CHARGE] ),
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
RETURN
amount + charge
)
BBF
Thank you for your reply. I tried the DAX formula, but the total does not align with the amount I summed up in Excel. I am not sure the root casue and the table model behind is complicated.
total amount in pbi: $1,605,325,840.19
total amount in excel: $1,049,974,834.94
katiehui can you share the pbix? it's really difficult to work on the measure without datas.
Try with:
expected_revenue =
IF (
ISINSCOPE ( DIM_CONTRACT_HEADER[CONTRACT_NUMBER] ),
VAR selected_dates = VALUES ( DIM_CALENDAR[YEAR_MONTHNAME] )
VAR contract = SELECTEDVALUE ( DIM_CONTRACT_HEADER[CONTRACT_NUMBER] )
VAR amount =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[PRINCIPALAMOUNT] ),
DIM_CONTRACT_LINE[ITEM_ID] IN { "ID_1", "ID_2" },
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
VAR charge =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[CHARGE] ),
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
RETURN
amount + charge,
-- When at Total row:
SUMX (
VALUES ( DIM_CONTRACT_HEADER[CONTRACT_NUMBER] ),
VAR selected_dates = VALUES ( DIM_CALENDAR[YEAR_MONTHNAME] )
VAR contract = DIM_CONTRACT_HEADER[CONTRACT_NUMBER]
VAR amount =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[PRINCIPALAMOUNT] ),
DIM_CONTRACT_LINE[ITEM_ID] IN { "ID_1", "ID_2" },
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
VAR charge =
CALCULATE (
SUM ( FACT_PAYMENT_SCHEDULE[CHARGE] ),
TREATAS ( selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME] ),
FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER] = contract
)
RETURN
amount + charge
)
)BBF
Sorry, I cannot share the pbi but the problem is solved. The data model has some problem and i have adjusted it and added a colunm to table to help the calculation. The dax is the same as above. Thanks again.
Hi katiehui,
We appreciate your efforts and are pleased to hear that your issue was resolved. Please mark the helpful response and "Accept as solution". This will assist other community members in resolving similar issues more efficiently.
Thank you.
