Income Statement Matrix Percentage

[TimmK]

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?

[VahidDM]

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!!

[TimmK]

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?