topic Re: Income Statement Matrix Percentage in DAX Commands and Tips

Income Statement Matrix Percentage

TimmK — Fri, 08 Oct 2021 07:40:01 GMT

For the income statement I want to create a matrix table:

Rows: Different categories (revenue, different types of expenses)
Columns: Years (2021, 2020, 2019 etc.)
Values: Amount (and percentage)

I want to compare the amount of 2021 to the amount of the previous years for each category. Yet, the formula for the percentage values DIVIDE(CALCULATE([Amount],'Date'[Year]=2021,[Amount],0) does not work, the % values remain empty when I apply the formula.

Here is how it looks like in Excel. How can I achieve this in Power BI?

Re: Income Statement Matrix Percentage

VahidDM — Sun, 10 Oct 2021 08:37:20 GMT

Hi TimmK

Try this measure:

% = VAR _2021 = CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Category] ), 'Table'[Year] = 2021 ) RETURN _2021 / SUM ( 'Table'[Amount] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Income Statement Matrix Percentage

TimmK — Thu, 14 Oct 2021 08:23:37 GMT

Hi VahidDM

Thank you for your great and helpful answer.

Yet, I actually have three tables that result in the income statement, namely the date, account and income table. How can I apply ALLEXCEPT here?