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 Statement IF ( SWITCH

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!!!

 

2018-05-07_19h11_11.png

 2018-05-07_19h16_06.png

3 REPLIES 3
Floriankx
Solution Sage
Solution Sage

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.

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/

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.