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

    )

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

)