Forum Discussion

katiehui's avatar
katiehui
Frequent Visitor
1 year ago
Solved

Incorrect total

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_MONT...
Need Help
  • katiehui's avatar
    katiehui
    1 year ago

    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.


BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
1 year ago

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's avatar
katiehui
Frequent Visitor
1 year ago

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's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.