Forum Discussion

Bart's avatar
Bart
Frequent Visitor
5 years ago
Solved

Matrix with extra forumulas - how do you do this ??

Hi All, 

I hope you have a solution for this.

I have a financial transaction table and want to make a matrix like the excel example below:

 

Excel example 

But what is the way to add some extra formules in this matrix, like :

Margin ( = Net turnover + Purchase )

- Total Costs - Operation Result and EBITDA ?

Is there a way to view this like the excel example?

 

This is the content of the table I imported

 

Regards, Bart

 

If nesseccary i can send a pbix example

 

  • With this model:

    Structure table

     and these measures:

     

     

    Sum Amount = SUM(Transactions[Amount])
    Sum Margin =
    CALCULATE (
        SUM ( Transactions[Amount] ),
        'Structure'[Order] IN { 1, 2 },
        ALL ( 'Structure' )
    )
    
    Sum Total Costs =
    CALCULATE (
        SUM ( Transactions[Amount] ),
        FILTER (
            ALL ( 'Structure' ),
            'Structure'[Order] > 2
                && 'Structure'[Order] < 11
        ),
        ALL ( 'Structure' )
    )
    
    Sum Operating Result =
    CALCULATE ( [Sum Total Costs] + [Sum Margin], ALL ( 'Structure' ) )
    
    EBITDA = 
    VAR FinInEx = CALCULATE([Sum Amount], 'Structure'[Order] = 13, ALL('Structure'))
    RETURN
    [Sum Operating Result] + FinInEx

     

     

    and the final measure for the visual:

     

     

    Amount =
    SWITCH (
        SELECTEDVALUE ( 'Structure'[Order] ),
        3, [Sum Margin],
        11, [Sum Total Costs],
        12, [Sum Operating Result],
        14, [EBITDA],
        [Sum Amount]
    )
    

     

     

    You get the following:

     

    I've attached the sample PBIX file

  • Bart's avatar
    Bart
    5 years ago

    Hi Paul, This looks very nice and is exactly what i searching for, thxs a lot !!!!

  • Ok, so you have a couple of options which are basically aesthetically driven choices. You can either have the following:

    Using...

     

    diff € vs. Last Year =
    IF (
        SELECTEDVALUE ( 'Date'[Year] ) = YEAR ( TODAY () ),
        [Amount] - CALCULATE ( [Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )
    )
    
    % diff vs Prev year =
    DIVIDE (
        [diff € vs. Last Year],
        CALCULATE ( [Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )
    )
    

     

     

    But this invloves hacking the matrix visual to hide the irrelevant columns and turning off "word wrap" in the column formatting options and dragging the column boundaries...

     

    or you can create this:

    using...

     

    This Year = CALCULATE([Amount], 'Date'[Year] = YEAR(TODAY()))
    Last Year = CALCULATE([Amount], 'Date'[Year] = YEAR(TODAY())-1)
    Diff vs last year = [This Year] - [Last Year]
    % diff vs last year = DIVIDE([Diff vs last year], [Last Year])

     

    But as you can see the column headings are not dynamic ("This Year" & "Last Year")

     

    So it is really up to you...

    I've attached the sample PBIX file.

     

13 Replies

  • Bart's avatar
    Bart
    Frequent Visitor

    Hi Paul, thanks but i can't see the video link

     

    • Bart's avatar
      Bart
      Frequent Visitor

      Hi Paul, its a nice video about PNL reporting, but it don't give an answers on my question if its is possible to add new rows with formulas

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion

        To build the structure you need a table with all the rows. If that is not possible at the source, you can create the structure in Excel and import it or paste it into a table using "Enter data"(including a sorting order column) or  you can create a new table by using the equivalent of the following as code for a new table option in the ribbon:

        all rows = 

        VAR _ExtraRows = {"NewRow1", "NewRow2", "NewRow3", "NewRow4"}
        RETURN

        UNION( DISTINCT('YourCurrent'[rows]), _ExtraRows)

         

        In the new table you will then need to establish the row order using a SWITCH function in a new calculated column. . You can then sort the row column using the "Sort column by" in the ribbon and selecting the order column. 

        If you are still stuck, please share a sample PBIX file