Forum Discussion

TimmK's avatar
TimmK
Helper IV
4 years ago

Profitbase Income Statement Percentage

I use the visual "Financial Reporting Matrix by Profitbase" to create an income statement with custom rows. The measures I use are Amount and %. The % is the amount of the specific year compared to the amount of the current year. For example, for sales the amount of 2022 is 100 and the amount of 2021 is 80. Thus, the amount of 2022 is 25% higher than the amount of 2021.

 

Unfortunately, as you can see below in the "Actual" table the % values are added in the custom row "Total Operating Income" instead of calculating the correct % as seen in the "Desired" table. How can I achieve the correct % values?

 

I want that it is still possible that I can use the year slicer, for example to add the years 2019 and 2018 or to remove years.

 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    TimmK , Can you share % formula


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • TimmK's avatar
      TimmK
      Helper IV
      4 years ago

      Yes, here is the % measure:

      % =
      DIVIDE(
         CALCULATE(
            [Sales],
            ALL('Date'[Date].[Year]),
            'Date'[Year]=YEAR(TODAY())
         ),
         [Sales],
         0

      )

      -1

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI TimmK,

    You can try to use the following measure formula if helps:

    % =
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            [Sales],
            FILTER ( ALLSELECTED ( Table ), 'Table'[Year] = YEAR ( currDate ) )
        ),
        [Sales],
        0
    ) - 1

    If that also doesn't help, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng