Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Bart
Frequent Visitor

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 

Bart_0-1630749336552.png

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

Bart_1-1630749482752.png

 

Regards, Bart

 

If nesseccary i can send a pbix example

 

3 ACCEPTED SOLUTIONS
PaulDBrown
Community Champion
Community Champion

With this model:

model.JPGStructure tableStructure 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:

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

View solution in original post

PaulDBrown
Community Champion
Community Champion

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

TI.JPG

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:

Measures.JPG

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.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
PaulDBrown
Community Champion
Community Champion

sorry I just fixed it





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Champion
Community 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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul, Teh structure is clear, but in what way can i add formulas to this structure? As the excel example. 

I can't upload here (no upload function i quess in my account) , but i share this pbix via onedrive: 

https://witteveenlogisticsit-my.sharepoint.com/:u:/g/personal/bart_witteveenlogisticsit_onmicrosoft_...

 

PaulDBrown
Community Champion
Community Champion

With this model:

model.JPGStructure tableStructure 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:

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Bart
Frequent Visitor

Hi Paul, one question about this solution.

Is it possible to get the comparision with last year too in one matrix ? 

PaulDBrown
Community Champion
Community Champion

Sure. Can you post a sample PBIX file with data?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul, 

Sorry for the delay, attached the one drive link to the pbix. See tab 2 of the pbix with an excel sample too:

https://witteveenlogisticsit-my.sharepoint.com/:u:/g/personal/bart_witteveenlogisticsit_onmicrosoft_...

 

Thanxs in advance

Regards, Bart

PaulDBrown
Community Champion
Community Champion

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

TI.JPG

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:

Measures.JPG

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.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul, nice, thank you for your quick response and solution!

Bart
Frequent Visitor

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

 

PaulDBrown
Community Champion
Community Champion

see if this video helps
PnL reporting 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.