Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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())
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
37 | |
31 | |
26 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |