How to Fake an Income Statement in Power BI (Tutorial)

I know these are not the most politically correct words; however, we cannot build a real Income Statement in Power BI the way we can in Excel. The best we can do is put together something that looks like an Income Statement and hope that our CFO will accept it, knowing very well, that there is no chance that he or she will love what we deliver. The reason for that is that we just simply don’t have all the formatting options that we have in Excel to properly format every line of our P&L, not to mention the drill down experience that we can provide in Excel.

However, we do not have an option, we must do what we can in Power BI, so here you go, I will demonstrate one way to fake it (and there are many). This will work well enough, although I will keep praying that Microsoft can develop a visual that will actually make this task easy for us to do.

You can download the PBIX report that I used in the video here.

Here is the DAX for the Income statement measure:

Income Statement Amount = SWITCH(SELECTEDVALUE(‘Income Statement'[Category]),
“Units”,FORMAT([Units],[formatWholeNumber]),
“Gross Revenue”,
IF(HASONEVALUE(‘Income Statement'[Line Item]),
SWITCH(SELECTEDVALUE(‘Income Statement'[Line Item]),
“Discounts & Allowances”, FORMAT([Discounts & Allowances], [formatUSD]),
” (% of Gross Revenue)”, FORMAT([DA as % of Gross Revenue], [formatPercent]),
“Returns”, FORMAT([Returns], [formatUSD])
), FORMAT([Gross Revenue], [formatUSD])
),
“Net Revenue”, FORMAT([Net Revenue], [formatUSD]),
“Cost of Goods Sold”, FORMAT([Cost of Goods Sold], [formatUSD]),
“___________________”, “”,
“Gross Profit”, FORMAT([Gross Profit], [formatUSD]),
“Gross Margin”, FORMAT([Gross Margin], [formatPercent]),
“Operating Expenses”,
IF(HASONEVALUE(‘Income Statement'[Line Item]),
SWITCH(SELECTEDVALUE(‘Income Statement'[Line Item]),
“SG&A”, FORMAT([SG&A], [formatUSD]),
” (% of Net Revenue)”, FORMAT([SG&A as % of Net Revenue], [formatPercent]),
“Other”, FORMAT([Other Operating Expenses], [formatUSD])
), FORMAT([Operating Expenses], [formatUSD])
),
“__________________”, “”,
“Operating Income”, FORMAT([Operating Income], [formatUSD]),
“Finance Costs”, FORMAT([Finance Costs], [formatUSD]),
“____________________”, “”,
“Profit Before Tax”, FORMAT([Profit Before Tax], [formatUSD]),
“Tax Expense”, FORMAT([Tax Expense], [formatUSD]),
“█████████████”, “”,
“Net Income”, FORMAT([Net Income], [formatUSD])
)