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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
toddpbi
Helper II
Helper II

Financial Statements using MATRIX Visual

Hello PBI Community,

 

I was wondering whether anyone could help me out on a specific problem I am having. I am trying to visualize a profit and loss statement in a matrix visualization similar to how I do with a PivotTable from Power Pivot in Excel. Despite the calculated measures in my pivot in excel (ie Gross Profit (GM1) ) are showing correctly in Power Pivot, they do not show within a Power BI matrix visual. Currently, I am using the following code to get my Pivot to display the way it does:

IS Subtotal Act =
IF (
    COUNTROWS ( VALUES ( DIM_Headers[Header] ) ) = 1,
    SWITCH (
        VALUES ( DIM_Headers[Header Order] ),
        1, [Rev Act],
        2, [CoS Act],
        3, [GM1 Act],
        4, [SCO Var Act],
        5, [SCO Fixed Act],
        6, [OOIE Act],
        7, [ADM Fix Act],
        8, [OVC Act],
        BLANK ()
    ),
    100
)

 

I then apply this measure in the values field list and apply my headers, subheaders and account names on the rows to try and get my report to display correctly.

 

2018-05-08_11h40_33.png

 

However, my end result looks something a little bit more like this:

 

2018-05-08_11h41_52.png

 

I have also tried to remove the potential blanks in the underlying tables for my GM1 measure, by applying ALL to the measure as follows:

GM1 Act =
CALCULATE ( [Rev Act] - [CoS Act]),
FILTER (
           ALL ( DIM_SubHeaders, DIM_SubHeaders[SubheaderName] ) )

This does not work either and returns an error.


What I am trying to achieve here is have my table display in the order of the measures by switching the values from the header table with the header order column below:

2018-05-08_11h32_27.png

 

However, when I do so, the GM1 (Gross margin o.PPC/cost of sales) measure [GM1 Act] is simply blank and does not contain a value. However, when I select drill down, the correct value is calculated GM1:= [Rev Act] - [CoS Act] (Revenue - Cost of Sales), which is 14,000,000 (approx).

This is a really important project to me and it would be a shame to throw away what I have done. I am not sure why the visual displays correctly in a Pivot in Excel, but not in Power BI. This table is related to the DIM_Accounts table which contains accounts relating to their specific header and subheader, and then down to the General Ledger table as pictured below.

 

2018-05-08_11h38_47.png

 

Do you have any idea why this would be the case?
Please help me out if you have time.

3 REPLIES 3
Ruan-BFI
Frequent Visitor

Hi

Was wondering if you found a solution for this as I am having the exact same issue.

Hi 

 

You might get some inspiration to solve the issue by looking at this method

http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivo...

 

/Erik

 

 

 

Thanks for referencing my article @donsvensen, but this would mean a complete re-work of this model.

 

@toddpbi: What happens if you eliminate the field "Subheader" from your report?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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