Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredGet Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more
I am building up a P&L sheet and am having trouble getting the switch statement to show the correct summary - 
Here is what I get and the total should be the sum of the second level of the hierarchy
  
CODE:
Measure Selection Kostenposten = 
VAR NetSales = ROUND(CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Net Sales"), 0) * -1
VAR COGS = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "COGS") * -1
VAR GrossProfitI = NetSales + COGS
VAR MarginI = FORMAT(DIVIDE(GrossProfitI,NetSales), "Percent")
VAR Freight = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "Freight") * -1
VAR GrossProfitII = GrossProfitI + Freight
VAR MarginII = FORMAT(DIVIDE(GrossProfitII, NetSales), "Percent")
VAR Wages = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Wages") * -1
VAR Vacation = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Vacation") * -1
VAR Bonus = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Bonus") * -1
VAR Commission = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Commission") * -1
VAR SocialCosts = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[Category III] = "Social Costs") * -1
VAR Salary = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "Salary") * -1
VAR OPEX = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "OPEX") * -1
VAR EBITDA = GrossProfitII + Salary + OPEX
VAR MarginIII = FORMAT(DIVIDE(EBITDA, NetSales), "Percent")
VAR FinancialOther = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "Financial: Other") * -1
VAR EBIT = EBITDA + FinancialOther
VAR GrossMarginI = FORMAT(DIVIDE(EBIT, NetSales), "Percent")
VAR FinancialExpense = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "Financial Expense") * -1
VAR Tax = CALCULATE(SUM(Kostenposten[Amount]), Kostenposten[P&L Category] = "Tax") * -1
VAR NetIncome = EBIT + FinancialExpense + Tax
VAR GrossMarginII = FORMAT( DIVIDE(NetIncome, NetSales), "Percent")
RETURN   
    IF(ISCROSSFILTERED('P&L Index'),
SWITCH( TRUE(),
    VALUES('P&L Index'[Category]) = "Net Sales", NetSales,
    VALUES('P&L Index'[Category]) = "COGS", COGS,
    VALUES('P&L Index'[Category]) = "Gross Profit I", NetSales + COGS,
    VALUES('P&L Index'[Category]) = "Margin % I", MarginI,
    VALUES('P&L Index'[Category]) = "Freight", Freight,
    VALUES('P&L Index'[Category]) = "Gross Profit II", GrossProfitII,
    VALUES('P&L Index'[Category]) = "Margin % II", MarginII,
    FIRSTNONBLANK('P&L Index'[Category II], 1) = "Wages", Wages,
    FIRSTNONBLANK('P&L Index'[Category II], 1) = "Vacation", Vacation,
    FIRSTNONBLANK('P&L Index'[Category II], 1) = "Bonus", Bonus,
    FIRSTNONBLANK('P&L Index'[Category II], 1) = "Social Costs", SocialCosts,
    FIRSTNONBLANK('P&L Index'[Category II], 1) = "Commision", Commission,
    VALUES('P&L Index'[Category]) = "Salary", Salary,
    VALUES('P&L Index'[Category]) = "OPEX", OPEX,
    VALUES('P&L Index'[Category]) = "EBITDA", EBITDA,
    VALUES('P&L Index'[Category]) = "Margin % III", MarginIII,
    VALUES('P&L Index'[Category]) = "Financial: Other", FinancialOther,
    VALUES('P&L Index'[Category]) = "EBIT", EBIT * -1,
    VALUES('P&L Index'[Category]) = "Gross Margin I", GrossMarginI,
    VALUES('P&L Index'[Category]) = "Financial Expense", FinancialExpense,
    VALUES('P&L Index'[Category]) = "Tax", Tax,
    VALUES('P&L Index'[Category]) = "Net Income", NetIncome,
    VALUES('P&L Index'[Category]) = "Gross Margin II", GrossMarginII, BLANK()), BLANK())
 
					
				
				
			
		
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
 
            | User | Count | 
|---|---|
| 80 | |
| 49 | |
| 35 | |
| 31 | |
| 30 |