Financial Statements using MATRIX Visual

[toddpbi]

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.

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

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:

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.

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

[Ruan-BFI]

Hi

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

[donsvensen]

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

[ImkeF]

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?