Incorrect total

[katiehui]

The expected revenue of each row is correct but the grand total is not correct. Could anyone help?

The dax I am using now:

expected_revenue =

var selected_dates = values(DIM_CALENDAR[YEAR_MONTHNAME])

var contract_inv = values(FACT_SALES_INVOICE_LINE[FK_DIM_CONTRACT_HEADER])

var amount = sumx(DIM_CONTRACT_HEADER, CALCULATE(

                    sum(FACT_PAYMENT_SCHEDULE[PRINCIPALAMOUNT]),

                    DIM_CONTRACT_LINE[ITEM_ID] IN {"ID_1", "ID_2"},

                    TREATAS(selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME]),

                    TREATAS(contract_inv, FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER])))

VAR charge = sumx(DIM_CONTRACT_HEADER, CALCULATE(

                    sum(FACT_PAYMENT_SCHEDULE[CHARGE]),

                    TREATAS(selected_dates, FACT_PAYMENT_SCHEDULE[YEARMONTHNAME]),

                    TREATAS(contract_inv, FACT_PAYMENT_SCHEDULE[FK_DIM_CONTRACT_HEADER])))

RETURN

 amount + charge

* the format of YEAR_MONTHNAME & YEARMONTHNAME is yyyy-MMM

[BeaBF]

@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

[katiehui]

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

[BeaBF]

@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

[katiehui · Accepted solution]

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.

[v-ssriganesh]

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.