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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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