Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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())
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |