Forum Discussion

cfo's avatar
cfo
Icon for Helper I rankHelper I
5 years ago

Need help using SWITCH function to add measure to a matrix

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!

 

 

Need Help

8 Replies

    • cfo's avatar
      cfo
      Icon for Helper I rankHelper I
      5 years ago

      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).  

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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