SWITCH function for P&L Statement

[johnny1]

Like many others, I'm trying to build a P&L statement but have hit a wall. My goal is to include "calculated rows" in a matrix such as the following:

PLID            Category

0	Gross Revenue
1	Terms Discount
2	Returns & Refunds
3	Net Revenue
4
5	COGS
6
7	Product Profit
8	Product Margin %
9
10	Freight Out
11	Total COGS
12
13	Gross Profit
14	Gross Margin %
15
16	Expenses
17	Trade Spend
18	Trade Events
19	Retail Marketing
20	Operating Expenses
21	Total Expenses
22
23	Net Profit
24	Net Margin

So far it appears the SWITCH function is the best workaround since Power BI lacks calculated rows. This is what I've tried using the column "PLID" as the anchor for the table I want to produce, but it fails miserably. Essentially, the values for all "SUM" functions pull in properly from a related table, but none of the "calculated measures" for margins or subtotals pull into the result. My guess is that there's an issue with the difference between some of the measures aggregating data from one source table and the rest of the calculated measures that are "measures of measures"? 

Thanks in advance for tips here... 

Switch Measure = 
    IF ( 
        HASONEVALUE('P&L Template'[PLID]),
            SWITCH( VALUES('P&L Template'[PLID]),
                0,[Sum Gross Revenue],
                1,[Sum Terms Discount],
                2,[Sum Returns & Refunds],
                3,[Net Revenue],
                4,"",         
                5,[Sum P&L COGS],
                6,"",
                7,[Product Profit],
                8,[Product Margin],
                9,"",
                10,[Sum Freight Out],
                11,[Total COGS],
                12,"",
                13,[Gross Profit],
                14,[Gross Margin],
                15,"",
                16,"",
                17,[Sum Trade Spend],
                18,[Sum Trade Events],
                19,[Sum Retail Marketing],
                20,[Sum Operating Expenses],
                21,[Total Expenses],
                22,"",
                23,[Net Profit],
                24,[Profit Margin %],
                BLANK(),"",
BLANK()            ))

[v-yuta-msft]

Hi johnny1,

"but none of the "calculated measures" for margins or subtotals pull into the result. My guess is that there's an issue with the difference between some of the measures aggregating data from one source table and the rest of the calculated measures that are "measures of measures"? "

<--- What's your expected result and could you please post the measures you are using and the "measures of measures" as you said? 

Regards,

Jimmy Tao

[Seward12533]

Can you provide more details about your model and measures?  What your doing should work, my guess its probably has to do with mulitple fact and lookup tables and the filter context not being applied the way you want. Ideally a sample PBIX with model and representative data vs actual data or at least pic of Model with relationsips and definitions of some of these measures you reference. and layout of matrix/visuals your trying to use.