Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Argument 4 Calculate formula Error

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')),

))))))))))

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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')),

    )

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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')),

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit where was my error???

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      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