Global Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Reduce Performance in Calculated Column

  • 1.  Reduce Performance in Calculated Column

    Bronze Contributor
    Posted Sep 23, 2019 01:10 PM
    This Calculate Column it's taking a lot of performance. I need to reduce because Power BI Service doesn't let me refresh automatically my reports.
    Any idea?

    Data Type = 
    //Actual
    
    //Metrics
    var ProductionCases = CALCULATE([Production Cases],'Master Table'[R Operational Area] <> BLANK())
    var Pounds = CALCULATE([Pounds],'Master Table'[R Operational Area] <> BLANK())
    var m2 = CALCULATE(SUM('Master Table'[R Operational Area])*10000)
    
    //Production Labor
    var HeadGrower = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "HEAD GROWER", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var BiocontrolSupervisor = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "BIO SUP", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var GreenhouseSupervisor = CALCULATE([Greenhouse Supervisor], 'Master Table'[R Operational Area] <> BLANK())
    var IrrigationSupervisor = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "IRRIG SUP", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var GreenhouseLabor = CALCULATE([Greenhouse Labor],'Master Table'[R Operational Area] <> BLANK())
    var BiocontrolLabor = CALCULATE([Biocontrol Labor],'Master Table'[R Operational Area] <> BLANK())
    //Production Materials & Expense
    var Fertilizer = CALCULATE([Fertilizer], 'Master Table'[R Operational Area] <> BLANK())
    var InsecticidesAgrochemicals = CALCULATE([Insecticides (agrochemicals)], 'Master Table'[R Operational Area] <> BLANK())
    var BeneficalsInsects = CALCULATE([Beneficals Insects],'Master Table'[R Operational Area] <> BLANK())
    var Pollinators = CALCULATE([Pollinators],'Master Table'[R Operational Area] <> BLANK())
    var BiocontrolMaterials = CALCULATE([Biocontrol Materials],'Master Table'[R Operational Area] <> BLANK())
    var CarbonDioxide = CALCULATE([Carbon Dioxide],'Master Table'[R Operational Area] <> BLANK())
    var NaturalGas = CALCULATE([Natural Gas],'Master Table'[R Operational Area] <> BLANK())
    var CropMaterials = CALCULATE([Crop Materials],'Master Table'[R Operational Area] <> BLANK())
    var CleaningDisinfectant = CALCULATE([Cleaning & Disinfectant Supplies],'Master Table'[R Operational Area] <> BLANK())
    var Electricity = CALCULATE([Electricity GH],'Master Table'[R Operational Area] <> BLANK())
    var  OtherGH = CALCULATE([Other GH Direct Mat&Exp(Paints,Coco,Plants,Gh Main,Testing)],'Master Table'[R Operational Area] <> BLANK())
    var ProductionIndirectExpense = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    //Production
    var HGTotalCost = HeadGrower+BiocontrolSupervisor+GreenhouseSupervisor+IrrigationSupervisor+GreenhouseLabor+BiocontrolLabor+Fertilizer+InsecticidesAgrochemicals+BeneficalsInsects+Pollinators+BiocontrolMaterials+CarbonDioxide+NaturalGas+CropMaterials+CleaningDisinfectant+Electricity+OtherGH
    var HGCostperPounds = DIVIDE(HGTotalCost,Pounds)
    
    //Operation
    var MaintenanceManager = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "MAINT MGR", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var MaintenanceCrew = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "MAINT CREW", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var CleaningLabor = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "CLEANING", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var Driver = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "BUSDRIVER", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var FoodSafety = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$G_L Entry'[Job No_] = "NF2017-01", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "FOOD SAFETY", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var OMTotalCost = MaintenanceCrew+MaintenanceManager+CleaningLabor+Driver+FoodSafety
    var OMCostperPounds = DIVIDE(OMTotalCost,Pounds)
    
    //Packing
    var PackhouseSupervisor = CALCULATE([Packhouse Supervisor], 'Master Table'[R Operational Area] <> BLANK())
    var PackhouseLabor = CALCULATE([Packhouse Labor], 'Master Table'[R Operational Area] <> BLANK())
    var PackhouseMaterials = CALCULATE([Packhouse Material], 'Master Table'[R Operational Area] <> BLANK())
    var PackhouseExpense = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5203-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var FreighttoDistribuitor = (CALCULATE([Production Cases],'Master Table'[R Operational Area] <> BLANK())/[Sales All Working Jobs])*[Freight to Distribuitor]
    var InandOut = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "5303-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var PHTotalCost = PackhouseSupervisor+PackhouseLabor+PackhouseMaterials+PackhouseExpense+FreighttoDistribuitor+InandOut
    var PHCostperPounds = DIVIDE(PHTotalCost,Pounds)
    
    //Administration
    var AdministrativeSalaries = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6301-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var CommonServices = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[Account 7 No] = "6301-02", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var SalesCommission = (CALCULATE([Gross Sales],'Master Table'[R Operational Area] <> BLANK())*.08)
    var SGA = (CALCULATE(CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[Concatenate Account No]>= 6102, 'WFA$G_L Entry'[Concatenate Account No]<=6302.98) -  (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6105-00-000-000") + CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6201-00-000-000") + CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[Concatenate Account No]>= 6301, 'WFA$G_L Entry'[Concatenate Account No]<=6301.99)), 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area] 
    var ADMINTotalCost = AdministrativeSalaries+CommonServices+SalesCommission+SGA
    var ADMINCostperPounds = DIVIDE(ADMINTotalCost,Pounds)
    
    //ITD&A
    var Rent = (CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6201-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var Interest =(CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "7300-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var Taxes = (CALCULATE(CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "7400-00-000-000") + CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "7401-00-000-000"), 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var Depreciation =  (CALCULATE(CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6500-00-000-000") + CALCULATE(SUM('WFA$G_L Entry'[Amount]), 'WFA$G_L Entry'[G_L Account No_] = "6500-02-000-000"), 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    
    //Income
    var GrossSales = CALCULATE([Gross Sales],'Master Table'[R Operational Area] <> BLANK())
    var OtherIncome = (CALCULATE(SUM('WFA$G_L Entry'[Amount])*-1, 'WFA$G_L Entry'[G_L Account No_] = "4306-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var NETREVENUE = GrossSales+OtherIncome
    
    //Metrics
    var TotalCost = HGTotalCost+OMTotalCost+PHTotalCost+ADMINTotalCost
    var NETPROFITEBITDA = NETREVENUE-TotalCost
    var PROFITAFTERITDA = NETPROFITEBITDA-(Rent+Interest+Taxes+Depreciation)
    var Costperm2 = DIVIDE(TotalCost,m2)
    var Profitperm2 = DIVIDE(NETPROFITEBITDA,m2)
    var Poundsoperm2 = DIVIDE(Pounds, m2)
    var CostperBox = DIVIDE(ProductionCases, TotalCost)
    var Costperpounds = DIVIDE(Pounds, TotalCost)
    var Profitperpounds = DIVIDE(NETPROFITEBITDA, Pounds)
    
    //Budget
    
    //Metrics
    var ProductionCasesB = CALCULATE([Production Cases (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var PoundsB = CALCULATE([Pounds (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var m2B = CALCULATE(SUM('Master Table'[R Operational Area])*10000)
    
    //Production Labor
    var HeadGrowerB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "HEAD GROWER", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var BiocontrolSupervisorB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "BIO SUP", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var GreenhouseSupervisorB = CALCULATE([Greenhouse Supervisor (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var IrrigationSupervisorB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "IRRIG SUP", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var GreenhouseLaborB = CALCULATE([Greenhouse Labor (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var BiocontrolLaborB = CALCULATE([Biocontrol Labor (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    //Production Materials & Expense
    var FertilizerB = CALCULATE([Fertilizer (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var InsecticidesAgrochemicalsB = CALCULATE([Insecticides (agrochemicals) (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var BeneficalsInsectsB = CALCULATE([Beneficals Insects (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var PollinatorsB = CALCULATE([Pollinators (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var BiocontrolMaterialsB = CALCULATE([Biocontrol Materials (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var CarbonDioxideB = CALCULATE([Carbon Dioxide (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var NaturalGasB = CALCULATE([Natural Gas (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var CropMaterialsB = CALCULATE([Crop Materials (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var CleaningDisinfectantB = CALCULATE([Cleaning & Disinfectant Supplies (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var ElectricityB = CALCULATE([Electricity GH (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var  OtherGHB = CALCULATE([Other GH Direct Mat&Exp(Paints,Coco,Plants,Gh Main,Testing) (Budget)], 'Master Table'[R Operational Area] <> BLANK())
    var ProductionIndirectExpenseB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS] = "", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    //Production
    var HGTotalCostB = HeadGrowerB+BiocontrolSupervisorB+GreenhouseSupervisorB+IrrigationSupervisorB+GreenhouseLaborB+BiocontrolLaborB+FertilizerB+InsecticidesAgrochemicalsB+BeneficalsInsectsB+PollinatorsB+BiocontrolMaterialsB+CarbonDioxideB+NaturalGasB+CropMaterialsB+CleaningDisinfectantB+ElectricityB+OtherGHB+ProductionIndirectExpenseB
    var HGCostperPoundsB = DIVIDE(HGTotalCostB,PoundsB)
    
    //Operation
    var MaintenanceManagerB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS]="MAINT MGR", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var MaintenanceCrewB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS]="MAINT CREW", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var CleaningLaborB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS]="CLEANING", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var DriverB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS]="BUSDRIVER", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var FoodSafetyB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5403-00-000-000", 'WFA$Dimension Set Transformed'[DEPARTMENTS]="FOOD SAFETY", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var OMTotalCostB = MaintenanceManagerB+MaintenanceCrewB+CleaningLaborB+DriverB+FoodSafetyB
    var OMCostperPoundsB = DIVIDE(OMTotalCostB, PoundsB)
    
    //Packing
    var PackhouseSupervisorB = CALCULATE([Packhouse Supervisor (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var PackhouseLaborB = CALCULATE([Packhouse Labor (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var PackhouseMaterialsB = CALCULATE([Packhouse Material (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var PackhouseExpenseB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5203-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var FreighttoDistribuitorB = CALCULATE([Freight to Distribuitor (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var InandOutB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "5303-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var PHTotalCostB = PackhouseSupervisorB+PackhouseLaborB+PackhouseMaterialsB+PackhouseExpenseB+FreighttoDistribuitorB+InandOutB
    var PHCostperPoundsB = DIVIDE(PHTotalCostB, PoundsB)
    
    //Administration
    var AdministrativeSalariesB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6301-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var CommonServicesB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[Account 7 No] = "6301-02", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var SalesCommissionB = CALCULATE([Gross Sales (Budget)],'Master Table'[R Operational Area] <> BLANK())*.08
    var SGAB = ((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[Concatenate Account No]>= 6102, 'WFA$G_L Budget Entry'[Concatenate Account No]<=6302.98, 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])-(((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6105-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])+((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6201-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])+((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[Concatenate Account No]>= 6301, 'WFA$G_L Budget Entry'[Concatenate Account No]<=6301.99, 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]))
    var ADMINTotalCostB = AdministrativeSalariesB+CommonServicesB+SalesCommissionB+SGAB
    var ADMINCostperPoundsB = DIVIDE(ADMINTotalCostB,PoundsB)
    
    //ITD&A
    var RentB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6201-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var InterestB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "7300-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var TaxesB = ((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "7400-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])+((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "7401-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])
    var DepreciationB =  ((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6500-00-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])+((CALCULATE(SUM('WFA$G_L Budget Entry'[Amount]), 'WFA$G_L Budget Entry'[G_L Account No_] = "6500-02-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area])
    
    //Income
    var GrossSalesB = CALCULATE([Gross Sales (Budget)],'Master Table'[R Operational Area] <> BLANK())
    var OtherIncomeB = (CALCULATE(SUM('WFA$G_L Budget Entry'[Amount])*-1, 'WFA$G_L Budget Entry'[G_L Account No_] = "4306-01-000-000", 'Master Table'[R Operational Area] <> BLANK())/[Real Operational Area])*'Master Table'[R Operational Area]
    var NETREVENUEB = GrossSalesB+OtherIncomeB
    
    //Metrics
    var TotalCostB = HGTotalCostB+OMTotalCostB+PHTotalCostB+ADMINTotalCostB
    var NETPROFITEBITDAB = NETREVENUEB-TotalCostB
    var PROFITAFTERITDAB = NETPROFITEBITDAB-(RentB+InterestB+TaxesB+DepreciationB)
    var Costperm2B = DIVIDE(TotalCostB,m2B)
    var Profitperm2B = DIVIDE(NETPROFITEBITDAB,m2B)
    var Poundsoperm2B = DIVIDE(PoundsB, m2B)
    var CostperBoxB = DIVIDE(ProductionCasesB, TotalCostB)
    var CostperpoundsB = DIVIDE(PoundsB, TotalCostB)
    var ProfitperpoundsB = DIVIDE(NETPROFITEBITDAB, PoundsB)
    
    var PercetageProduction = DIVIDE(Pounds, PoundsB)
    var PercentageNetchangeprofitm2 = DIVIDE(Profitperm2, Profitperm2B)
    var HGPercentageCostperPounds = DIVIDE(HGTotalCost, HGTotalCostB)
    var OMPercentageCostperPounds = DIVIDE(OMTotalCost, OMTotalCostB)
    var PHPercentageCostperPounds = DIVIDE(PHTotalCost, PHTotalCostB)
    var ADMINPercentageCostperPounds = DIVIDE(ADMINTotalCost, ADMINTotalCostB)
    
        Return
            IF('Master Table'[Account] = "Actual",
                SWITCH('Master Table'[Line],
                    "1.- Head Grower", HeadGrower,
                    "2.- Biocontrol Supervisor", BiocontrolSupervisor,
                    "3.- Greenhouse Supervisor", GreenhouseSupervisor,
                    "4.- Irrigation Supervisor", IrrigationSupervisor,
                    "5.- Greenhouse Labor", GreenhouseLabor,
                    "6.- Biocontrol Labor", BiocontrolLabor,
                    "1.- Fertilizer", Fertilizer,
                    "2.- Insecticides (Agrochemicals)", InsecticidesAgrochemicals,
                    "3.- Beneficals Insects", BeneficalsInsects,
                    "4.- Pollinators", Pollinators,
                    "5.- Biocontrol Materials", BiocontrolMaterials,
                    "6.- Carbon Dioxide", CarbonDioxide,
                    "7.- Natural Gas", NaturalGas,
                    "8.- Crop Materials", CropMaterials,
                    "9.- Cleaning & Disinfectant Supplies", CleaningDisinfectant,
                    "91.- Electricity", Electricity,
                    "92.- Other GH Direct Mat&Exp(Paints,Coco,Plants,Gh Main,Testing)", OtherGH,
                    "93.- Production Indirect Expense", ProductionIndirectExpense,
                    "1.- HG Total Cost", HGTotalCost,
                    "2.- HG Cost per Pounds", HGCostperPounds,
                    "3.- HG % Cost per Pounds", HGPercentageCostperPounds,
                    "1.- Maintenance Manager", MaintenanceManager,
                    "2.- Maintenance Crew", MaintenanceCrew,
                    "3.- Cleaning Labor", CleaningLabor,
                    "4.- Driver", Driver,
                    "5.- Food Safety", FoodSafety,
                    "6.- OM Total Cost", OMTotalCost,
                    "7.- OM Cost per Pounds", OMCostperPounds,
                    "8.- OM % Cost per Pounds", OMPercentageCostperPounds,
                    "1.- Packhouse Supervisor", PackhouseSupervisor,
                    "2.- Packhouse Labor", PackhouseLabor,
                    "3.- Packhouse Materials", PackhouseMaterials,
                    "4.- Packhouse Expense", PackhouseExpense,
                    "5.- Freight to Distribuitor", FreighttoDistribuitor,
                    "6.- In and Out Warehouse Expense", InandOut,
                    "7.- PH Total Cost", PHTotalCost,
                    "8.- PH Cost per Pounds", PHCostperPounds,
                    "9.- PH % Cost per Pounds", PHPercentageCostperPounds,
                    "1.- Administrative Salaries", AdministrativeSalaries,
                    "2.- Common Services", CommonServices,
                    "3.- Sales Commission", SalesCommission,
                    "4.- SG&A Expense", SGA,
                    "6.- ADMIN Total Cost", ADMINTotalCost,
                    "7.- ADMIN Cost per Pounds", ADMINCostperPounds,
                    "8.- ADMIN % Cost per Pounds", ADMINPercentageCostperPounds,
                    "1.- Rent", Rent,
                    "2.- Interest", Interest,
                    "3.- Taxes", Taxes,
                    "4.- Depreciation", Depreciation,
                    "1.- Gross Sales", GrossSales,
                    "2.- Other Income", OtherIncome,
                    "3.- NET REVENUE", NETREVENUE,
                    "1.- Production Cases", ProductionCases,
                    "2.- Pounds", Pounds,
                    "8.- Cost per Box", CostperBox,
                    "9.- Cost per Pound", Costperpounds,
                    "2.- Net Profit (EBITDA)", NETPROFITEBITDA,
                    "Profit After ITD&A", PROFITAFTERITDA,
                    "91.- Profit per Pound", Profitperpounds,
                    "1.- Total Cost", TotalCost,
                    "4.- Cost per m2", Costperm2,
                    "5.- Profit per m2", Profitperm2,
                    "6.- Pounds per m2", Poundsoperm2,
                    "3.- % Production", PercetageProduction,
                    "7.- % Net change profit/m2", PercentageNetchangeprofitm2,
                    BLANK()),
                SWITCH('Master Table'[Line],
                    "1.- Head Grower", HeadGrowerB,
                    "2.- Biocontrol Supervisor", BiocontrolSupervisorB,
                    "3.- Greenhouse Supervisor", GreenhouseSupervisorB,
                    "4.- Irrigation Supervisor", IrrigationSupervisorB,
                    "5.- Greenhouse Labor", GreenhouseLaborB,
                    "6.- Biocontrol Labor", BiocontrolLaborB,
                    "1.- Fertilizer", FertilizerB,
                    "2.- Insecticides (Agrochemicals)", InsecticidesAgrochemicalsB,
                    "3.- Beneficals Insects", BeneficalsInsectsB,
                    "4.- Pollinators", PollinatorsB,
                    "5.- Biocontrol Materials", BiocontrolMaterialsB,
                    "6.- Carbon Dioxide", CarbonDioxideB,
                    "7.- Natural Gas", NaturalGasB,
                    "8.- Crop Materials", CropMaterialsB,
                    "9.- Cleaning & Disinfectant Supplies", CleaningDisinfectantB,
                    "91.- Electricity", ElectricityB,
                    "92.- Other GH Direct Mat&Exp(Paints,Coco,Plants,Gh Main,Testing)", OtherGHB,
                    "93.- Production Indirect Expense", ProductionIndirectExpenseB,
                    "1.- HG Total Cost", HGTotalCostB,
                    "2.- HG Cost per Pounds", HGCostperPoundsB,
                    "1.- Maintenance Manager", MaintenanceManagerB,
                    "2.- Maintenance Crew", MaintenanceCrewB,
                    "3.- Cleaning Labor", CleaningLaborB,
                    "4.- Driver", DriverB,
                    "5.- Food Safety", FoodSafetyB,
                    "6.- OM Total Cost", OMTotalCostB,
                    "7.- OM Cost per Pounds", OMCostperPoundsB,
                    "1.- Packhouse Supervisor", PackhouseSupervisorB,
                    "2.- Packhouse Labor", PackhouseLaborB,
                    "3.- Packhouse Materials", PackhouseMaterialsB,
                    "4.- Packhouse Expense", PackhouseExpenseB,
                    "5.- Freight to Distribuitor", FreighttoDistribuitorB,
                    "6.- In and Out Warehouse Expense", InandOutB,
                    "7.- PH Total Cost", PHTotalCostB,
                    "8.- PH Cost per Pounds", PHCostperPoundsB,
                    "1.- Administrative Salaries", AdministrativeSalariesB,
                    "2.- Common Services", CommonServicesB,
                    "3.- Sales Commission", SalesCommissionB,
                    "4.- SG&A Expense", SGAB,
                    "6.- ADMIN Total Cost", ADMINTotalCostB,
                    "7.- ADMIN Cost per Pounds", ADMINCostperPoundsB,
                    "1.- Rent", RentB,
                    "2.- Interest", InterestB,
                    "3.- Taxes", TaxesB,
                    "4.- Depreciation", DepreciationB,
                    "1.- Gross Sales", GrossSalesB,
                    "2.- Other Income", OtherIncomeB,
                    "3.- NET REVENUE", NETREVENUEB,
                    "1.- Production Cases", ProductionCasesB,
                    "2.- Pounds", PoundsB,
                    "8.- Cost per Box", CostperBoxB,
                    "9.- Cost per Pound", CostperpoundsB,
                    "2.- Net Profit (EBITDA)", NETPROFITEBITDAB,
                    "Profit After ITD&A", PROFITAFTERITDAB,
                    "91.- Profit per Pound", ProfitperpoundsB,
                    "1.- Total Cost", TotalCostB,
                    "4.- Cost per m2", Costperm2B,
                    "5.- Profit per m2", Profitperm2B,
                    "6.- Pounds per m2", Poundsoperm2B,
                    BLANK()))
    ​


    ------------------------------
    Alberto Alvarado
    Business Intelligence
    Amado AZ
    5202819233
    ------------------------------