Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
arichard19
Resolver I
Resolver I

Switch statement within a hierarchy - incorrect sum at first level of the hierarchy

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

  Salary Switch Statements.PNG

 

 

 

 

 

 

 

 

 

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())

 

1 REPLY 1
amitchandak
Super User
Super User

@arichard19 , refer if these can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.