Argument 4 Calculate formula Error

[Anonymous]

Hi Experts

I cannot find the error in the following DAX...

CY =

SWITCH(

    SELECTEDVALUE('DimP&L'[Item]),

    "Gross Sales", SUM('Sales&Ohd_Data_Master'[Gross_Sales_USD])/1000,

    "Rebate", CALCULATE(SUM('Sales&Ohd_Data_Master'[Local_Rebates_USD])/1000, REMOVEFILTERS('DimP&L')),

    "Stock Cleanse", CALCULATE(SUM('Sales&Ohd_Data_Master'[Stock_Cleanse_USD])/1000, REMOVEFILTERS('DimP&L')),

    "Net Sales", CALCULATE(SUM('Sales&Ohd_Data_Master'[Net_Sales_USD])/1000, REMOVEFILTERS('DimP&L')),

    "COGS", CALCULATE(SUM('Sales&Ohd_Data_Master'[Gross_Cost_USD])/1000, REMOVEFILTERS('DimP&L')),

    "Gross Margin", CALCULATE(SUM('Sales&Ohd_Data_Master'[Factory_Margin_USD])/1000, REMOVEFILTERS('DimP&L')),

    "WD", CALCULATE(SUM('Sales&Ohd_Data_Master'[8.SC&L True-up])/1000, REMOVEFILTERS('DimP&L')),

    "OCOGS", CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Indirect])/1000,         REMOVEFILTERS('DimP&L')),

    "IBG", CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Indirect])/1000, REMOVEFILTERS('DimP&L')),

    "Contribution Margin", CALCULATE(SUM('Sales&Ohd_Data_Master'[Factory_Margin_USD])/1000-CALCULATE(SUM('Sales&Ohd_Data_Master'[8.SC&L True-up])/1000-CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Indirect])/1000)-CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Indirect])/1000, REMOVEFILTERS('DimP&L')),

    "Manufacturing & Engineering", CALCULATE(SUM('Sales&Ohd_Data_Master'[MFG+ENG+OCOGS Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[MFG+ENG+OCOGS Indirect])/1000, REMOVEFILTERS('DimP&L')),

    "SG&A", CALCULATE(SUM('Sales&Ohd_Data_Master'[SG&A Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[SG&A Indirect])/1000, REMOVEFILTERS('DimP&L')),

    "D&A", CALCULATE(SUM('Sales&Ohd_Data_Master'[D&A Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[D&A Indirect])/1000, REMOVEFILTERS('DimP&L')),

    "OI", CALCULATE(SUM('Sales&Ohd_Data_Master'[OI])/1000, REMOVEFILTERS('DimP&L')),

))))))))))

[Anonymous]

Hi Amit where was my error???

[amitchandak]

@Anonymous , Quite a few places calculate was not ending that selection. and closed them all at end

example

"OCOGS", CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Indirect])/1000, REMOVEFILTERS('DimP&L')),

"IBG", CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Direct])/1000+CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Indirect])/1000, REMOVEFILTERS('DimP&L')),

They are not closing properly

[amitchandak · Accepted solution]

@Anonymous , Try like

SWITCH(

SELECTEDVALUE('DimP&L'[Item]),

"Gross Sales", SUM('Sales&Ohd_Data_Master'[Gross_Sales_USD])/1000,

"Rebate", CALCULATE(SUM('Sales&Ohd_Data_Master'[Local_Rebates_USD])/1000, REMOVEFILTERS('DimP&L')),

"Stock Cleanse", CALCULATE(SUM('Sales&Ohd_Data_Master'[Stock_Cleanse_USD])/1000, REMOVEFILTERS('DimP&L')),

"Net Sales", CALCULATE(SUM('Sales&Ohd_Data_Master'[Net_Sales_USD])/1000, REMOVEFILTERS('DimP&L')),

"COGS", CALCULATE(SUM('Sales&Ohd_Data_Master'[Gross_Cost_USD])/1000, REMOVEFILTERS('DimP&L')),

"Gross Margin", CALCULATE(SUM('Sales&Ohd_Data_Master'[Factory_Margin_USD])/1000, REMOVEFILTERS('DimP&L')),

"WD", CALCULATE(SUM('Sales&Ohd_Data_Master'[8.SC&L True-up])/1000, REMOVEFILTERS('DimP&L')),

"OCOGS", (CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Indirect])/1000, REMOVEFILTERS('DimP&L'))),

"IBG", (CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Indirect])/1000, REMOVEFILTERS('DimP&L'))),

"Contribution Margin", (CALCULATE(SUM('Sales&Ohd_Data_Master'[Factory_Margin_USD])/1000)-CALCULATE(SUM('Sales&Ohd_Data_Master'[8.SC&L True-up])/1000)-CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[9.OCOGS_Indirect])/1000)-CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[11f IBG_Indirect])/1000, REMOVEFILTERS('DimP&L'))),

"Manufacturing & Engineering", (CALCULATE(SUM('Sales&Ohd_Data_Master'[MFG+ENG+OCOGS Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[MFG+ENG+OCOGS Indirect])/1000, REMOVEFILTERS('DimP&L'))),

"SG&A", CALCULATE(SUM('Sales&Ohd_Data_Master'[SG&A Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[SG&A Indirect])/1000, REMOVEFILTERS('DimP&L')),

"D&A", CALCULATE(SUM('Sales&Ohd_Data_Master'[D&A Direct])/1000)+CALCULATE(SUM('Sales&Ohd_Data_Master'[D&A Indirect])/1000, REMOVEFILTERS('DimP&L')),

"OI", CALCULATE(SUM('Sales&Ohd_Data_Master'[OI])/1000, REMOVEFILTERS('DimP&L')),

)