Forum Discussion

MCG's avatar
MCG
Helper I
5 years ago
Solved

Formatted Matrix table

i need to have that kinf of table format ( columns added as measures shold have samewformat 😞

I managed to achieve this via matrix and subtotals based on that Excel template:

 

The problem is with adding measures.

Measure calculation should be based on name Measure_Name but bold rows are categories and the rest are PNL lines...

i have no idea how to manage this:(

br

MCG

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi MCG ,

     

    Please try something like:

     

    Measure =
IF (
    ISINSCOPE ( 'Table'[PNL LINE] ),
    SWITCH (
        MAX ( 'Table'[PNL LINE] ),
        "Research and Development", [PLM_C_RD],
        "Sales and Distribution", [PLM_C_SD],........
    ),
    SWITCH (
        MAX ( 'Table'[PNL KAT] ),
        "Sales Revenue", [PLM_SALE],
        "Gross Margin", [PLM_MRG],........
    )
)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Familiarize yourself with the  HASONEVALUE and ISINSCOPE functions.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi MCG ,

     

    Please try something like:

     

    Measure =
IF (
    ISINSCOPE ( 'Table'[PNL LINE] ),
    SWITCH (
        MAX ( 'Table'[PNL LINE] ),
        "Research and Development", [PLM_C_RD],
        "Sales and Distribution", [PLM_C_SD],........
    ),
    SWITCH (
        MAX ( 'Table'[PNL KAT] ),
        "Sales Revenue", [PLM_SALE],
        "Gross Margin", [PLM_MRG],........
    )
)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai