Need help using SWITCH function to add measure to a matrix

[cfo]

Hi -

I built a P&L Statement using a matrix from this link. https://youtu.be/W4zEYkDC3Tw.  I've found this to be a brilliant solution, but I need to take it an additional step further.  I have a measure for Gross Margin % that I need to add to Row 4 of my matrix under the value for Gross Profit.  I've been trying to use the Switch Measure, but it shows up blank on the matrix.  I'm wondering if it's because the matrix is additive and the Gross Margin % is using Divide? Additionally, Gross Profit is a sub-total so it's not actually in the "real data".  I'm so close, I know there has to be a way to do this, I would appreciate any help you can offer with respect to using Switch to add measures to rows, specifically within the framework where I've used rolling totals for the P&L. 

Thanks!

[v-shex-msft]

Hi @cfo,

For your requirement, you need to create a calculated table to extract the raw category field values and add custom types, then use it to replace raw fields.
After these steps, you can use switch function with selected value to check current category value and replace the specific value calculation with custom expressions.

Regards,

Xiaoxin Sheng

[Stachu]

have a look here, it's a very similar problem
https://community.powerbi.com/t5/Desktop/Create-calculated-row/td-p/440613

[cfo]

Hi Stachu - That's exactly the same measure I've used from that same article, but I'm getting blanks on the rows where I expect Gross Margin values and can't figure out why it won't work with Matt Allington's P&L framewrork (I sent the link in my original post).  

[v-shex-msft]

HI @cfo,

Can you please share some dummy data that keep the raw data structure and the expected results to help us clarify your scenario? It is hard to troubleshoot from your description without any detailed records.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[cfo]

https://1drv.ms/u/s!AsXo8ULoMwCpplJgHWsZZsswo4LP?e=FipebP

Please see the attached file above. You will see the measures I tried to use, but can't get the Gross Margin and EBITDA Margins to appear in the Matrix.  

[v-shex-msft]

HI @cfo,

I succeed open your sample data and do some tests and I can add conditions to find out the row content that you want to be replaced. Now I found the trouble part is the 'Gross Margin' category, it seems not mapping any records in the 'P&L' table.
Since you created the relationship between two tables, the relationship filter will apply to these records. ('Gross Margin' category value will be filtered due to no match relationships keys and if you force it displayed power bi will also bring other records that already filtered)

Below are some samples of testing results.

Formula with low precision filter:

Correct Display Amt(modify) = 
VAR CalcType =
    SELECTEDVALUE ( 'Header Table'[CalcType] )
VAR Result =
    IF ( CalcType = 1, [Magic Additive Total 2], [Running Total] )
VAR currHeader =
    SELECTEDVALUE ( 'Header Table'[Header] )
VAR currDate =
    MAX ( 'Date Table'[FullDateAlternateKey] )
VAR list =
    SUMMARIZE ( FILTER ( ALLSELECTED ( 'P&L' ), Result <> BLANK () ), [Date] )
RETURN
    IF (
        currHeader IN { "Gross Margin", "EBITDA Margin" },
         -1,
        Result
    )

Accurate filter measure:

Correct Display Amt(modify) = 
VAR CalcType =
    SELECTEDVALUE ( 'Header Table'[CalcType] )
VAR Result =
    IF ( CalcType = 1, [Magic Additive Total 2], [Running Total] )
VAR currHeader =
    SELECTEDVALUE ( 'Header Table'[Header] )
VAR currDate =
    MAX ( 'Date Table'[FullDateAlternateKey] )
VAR list =
    SUMMARIZE ( FILTER ( ALLSELECTED ( 'P&L' ), Result <> BLANK () ), [Date] )
RETURN
    IF (
        currHeader IN { "Gross Margin", "EBITDA Margin" },
        IF ( currDate IN list, -1 ),
        Result
    )

Notice: '-1' is a placeholder that I used to put replace measure results, you can find the 'Gross Margin' part has been filtered by relationship. If force display them, it will take back the wrong records(red marked)

For this scenario, you need to break the relationship and use expression level filtered based on current header table values to apply the filter on calculations results.

Regards,

Xiaoxin Sheng

[cfo]

Thank you, I think we're almost there, I believe you are saying that I need to break the relationship between my Header table and the table containing the "real values".  Once I break the relationship however, it doesn't display the values correctly because it's not connected to my date table.  So, that's what I think your code above is helping me with.  Are you saying that once I add the code you provided above, that I don't need to use the Switch statement at all, and that Gross Margin% and EBITDA % will appear properly?

[cfo]

Can you also show me how I can get the Gross Margin and EBITDA Margin to appear as % in the matrix? Are you able to attach the sample table with the expected results? Thanks!