Calculate Total Costs from Multiple Tables

[Anonymous]

Hi 

I need to calculate the costs for a job the formula for which is 

 (income for the reporting period/total job income(price)) * total job cost. 

Income for this period is from the General Ledger Entries table and Total Job Income/Cost are from the Job Ledger Entries.

I have the following measure which calculates correctly for the job line in the matrix but as the total is a calculation based on all jobs in doesn't match the total of the indvidual jobs.

I know I have gone about it the wrong way but I'm stuck on how to correct it. Would appreciate any advice on how this could be done. Many thanks

#Total Deferred Cost = 
VAR _To =
   CALCULATE(
        MAX ( 'Calendar'[Date] ),
        ALLSELECTED ('Calendar'[Date] ))
    
VAR _From =
    CALCULATE (
        MIN (  'Calendar'[Date] ),
        ALLSELECTED (  'Calendar'[Date]))    

VAR _DefInc =
//Income values relating to this period
DIVIDE( 
CALCULATE (  
        SUMX ( 
        FILTER (
            General_Ledger_Entries,
            General_Ledger_Entries[G_L_Account_No] = "20130"
              && General_Ledger_Entries[Posting_Date] >= _From
              && General_Ledger_Entries[Posting_Date] <= _To
              && General_Ledger_Entries[Document_Type] = "Invoice"),General_Ledger_Entries[Debit_Amount]         
        )
    ),
//Total Income Value  =
   (CALCULATE( CALCULATE (
        SUMX( 
        FILTER (
            Job_Ledger_Entries,
            Job_Ledger_Entries[Entry_Type] = "Sale"
            && Job_Ledger_Entries[WIP] = "FIXEDPRICE"
            && Related(Grpd_General_Ledger_Entries[Job_No]) <> Blank()), Job_Ledger_Entries[Total_Price] 
        )
    ),REMOVEFILTERS('Calendar'[Date] ))*-1))*
//Total Cost Value
   CALCULATE( CALCULATE (
        SUMX ( 
        FILTER (
            Job_Ledger_Entries,
            Job_Ledger_Entries[Entry_Type] = "Usage"
            && Job_Ledger_Entries[WIP] = "FIXEDPRICE"
            && Related(Grpd_General_Ledger_Entries[Job_No]) <> Blank()), Job_Ledger_Entries[Total_Cost] 
        )
    ),REMOVEFILTERS('Calendar'[Date] ))
RETURN
_DefInc

[Anonymous]

Hi @Anonymous 

Thanks for the getting in contact. I think I'm fairly close to resolving it now. I'll post the solution if that proves correct or some data if not.

kind regards

Mark

[Anonymous]

Hi @Anonymous ,

Would you please share some sample data and expected result so that we could test the formula?

Best Regards,

Jay

[Anonymous · Accepted solution]

#Total Deferred Cost = 
VAR _To =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR _From =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR _DeferredCostsTbl =
    ADDCOLUMNS (
        SUMMARIZE ( ALL('Job_Ledger_Entries'), Job_Ledger_Entries[Job_No] ),
        "DeferredCosts",
            DIVIDE (
          CALCULATE(  CALCULATE (
                    SUMX (
                      FILTER (
                           (General_Ledger_Entries),
                            General_Ledger_Entries[G_L_Account_No] = "20130"
                                && General_Ledger_Entries[Posting_Date] >= _From
                                && General_Ledger_Entries[Posting_Date] <= _To
                                && General_Ledger_Entries[Document_Type] = "Invoice"
                        ),
                        General_Ledger_Entries[Debit_Amount]
                    )
                ),REMOVEFILTERS('Calendar')) ,
                CALCULATE (
                    CALCULATE (
                        SUMX (
                            FILTER (
                                Job_Ledger_Entries,
                                Job_Ledger_Entries[Entry_Type] = "Sale"
                                    && Job_Ledger_Entries[WIP] = "FIXEDPRICE"
                                    && RELATED ( Grpd_General_Ledger_Entries[Job_No] ) <> BLANK ()
                            ),
                            Job_Ledger_Entries[Total_Price]
                        )
                    ),
                    REMOVEFILTERS ( 'Calendar'[Date] )
                ) * -1
            )
                * CALCULATE (
                    CALCULATE (
                        SUMX (
                            FILTER (
                                Job_Ledger_Entries,
                                Job_Ledger_Entries[Entry_Type] = "Usage"
                                    && Job_Ledger_Entries[WIP] = "FIXEDPRICE"
                                    && RELATED ( Grpd_General_Ledger_Entries[Job_No] ) <> BLANK ()
                            ),
                            Job_Ledger_Entries[Total_Cost]
                        )
                    ),
                    REMOVEFILTERS ( 'Calendar'[Date] )
                )
    )
VAR _DeferredCosts =
  SUMX ( _DeferredCostsTbl, [DeferredCosts] )
RETURN
 _DeferredCosts

I managed to get this issue resolved by creating a table with the calculation included then used SUMX to iterate through.