Financial Statement IF ( SWITCH

[toddpbi]

Hello there PBI community,

Recently I have been referencing an article to learn more about DAX language in an attempt to create a P&L statement within Power BI Desktop. The problem I am facing is that calculated members of my report are showing as blank within the matrix visual, but show fine within a PivotTable within excel.

My calculated members are GM1 (Gross margin o. PPC/cost of sales) which for simplicities sake, I am calculating by simply subtracting [Total Revenue] - [Cost of Sales] measures. When I drag the measure on the canvas, it shows correctly as 14,082,453. This is also the total figure I get when drilling down to GM1. However, it does not display and simply displays as a blank value in the matrix visual.

I have used this DAX formula in order to arrange my FS in the order of the calculated measures I require:

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
)

My non-calculated measures (such as the sums of revenue and cost of goods sold, however, show correctly in the matrix. Is there any potential way to get these to show up within the matrix visual in Power BI? I have tried for hours on this with no result and it is getting rather frustrating.....

Any help would be greatly greatly appreciated!!!

 

[Floriankx]

Hello,

it is not that trivial to figure out without knowing the structure of your raw data.

Nontheless I think ALL should help you. I guess GM should be something like:

GM=CALCULATE([Rev Act]-[CoS Act],ALL(Table[SubCategories]).

For a more complicated P&L check here.

[toddpbi]

Apologies, this is the correct link to the model:
https://powerpivotpro.com/2018/01/star-schema-switch-drill-income-statement-design/

[toddpbi]

Hello,

Thanks for your response. The source table comes from this blog post. You can download the raw data as well as see the structure of the tables here by downloading the excel file: http://www.thebiccountant.com/2017/01/16/easy-profit-loss-and-other-account-statements-part2/