Forum Discussion

TimmK's avatar
TimmK
Helper IV
4 years ago

Income Statement Matrix Percentage

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?

Help Requested

2 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      TimmK
      Helper IV
      4 years ago

      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?