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_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

 

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.


5 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

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

      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's avatar
        BeaBF
        Super 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