Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Matrix Total Column

Hi All! Can I take different values for 'Total Column' and 'Values' in Matrix or Matrix Preview?

I have 6 calculated measures/columns. Those are Target Qty, Ach Qty, Ach%, Target Value $, Ach Val $, Val%.

The matrix I need is like below:

SKU 1 and SKU 2 columns should be quantities and 'Total Column' should be sum of values of both SKUs.

Please help!

Thank You!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    Based on below data view structure (in my test, Target Qty, Ach Qty, Ach%, Target Value , Ach Value, Val% are all calculated columns), we can get the desired matrix output.

     

    First, we should add an extra column into above table.

    Total = "Total"

     

    Then, create several calculated tables.

    New Table1 =
SELECTCOLUMNS (
    Matrix,
    "Sector", Matrix[Sector],
    "SKU", Matrix[Total],
    "Target Qty", Matrix[Target Value],
    "Ach Qty", Matrix[Ach Value],
    "Ach%", Matrix[Val%]
)

New Table1_1 =
SUMMARIZE (
    'New Table1',
    'New Table1'[Sector],
    "SKU", LASTNONBLANK ( 'New Table1'[SKU], 1 ),
    "Target Qty", AVERAGE ( 'New Table1'[Target Qty] ),
    "Ach Qty", AVERAGE ( 'New Table1'[Ach Qty] ),
    "Ach%", AVERAGE ( 'New Table1'[Ach%] )
)

New Table2 =
UNION (
    SELECTCOLUMNS (
        Matrix,
        "Sector", Matrix[Sector],
        "SKU", Matrix[SKU],
        "Target Qty", Matrix[Target Qty],
        "Ach Qty", Matrix[Ach Qty],
        "Ach%", Matrix[Ach%]
    ),
    'New Table1_1'
)

    Then, we can add a matrix whose fields are referring to 'New Table2'.

     

    Best regards,
    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    Based on below data view structure (in my test, Target Qty, Ach Qty, Ach%, Target Value , Ach Value, Val% are all calculated columns), we can get the desired matrix output.

     

    First, we should add an extra column into above table.

    Total = "Total"

     

    Then, create several calculated tables.

    New Table1 =
SELECTCOLUMNS (
    Matrix,
    "Sector", Matrix[Sector],
    "SKU", Matrix[Total],
    "Target Qty", Matrix[Target Value],
    "Ach Qty", Matrix[Ach Value],
    "Ach%", Matrix[Val%]
)

New Table1_1 =
SUMMARIZE (
    'New Table1',
    'New Table1'[Sector],
    "SKU", LASTNONBLANK ( 'New Table1'[SKU], 1 ),
    "Target Qty", AVERAGE ( 'New Table1'[Target Qty] ),
    "Ach Qty", AVERAGE ( 'New Table1'[Ach Qty] ),
    "Ach%", AVERAGE ( 'New Table1'[Ach%] )
)

New Table2 =
UNION (
    SELECTCOLUMNS (
        Matrix,
        "Sector", Matrix[Sector],
        "SKU", Matrix[SKU],
        "Target Qty", Matrix[Target Qty],
        "Ach Qty", Matrix[Ach Qty],
        "Ach%", Matrix[Ach%]
    ),
    'New Table1_1'
)

    Then, we can add a matrix whose fields are referring to 'New Table2'.

     

    Best regards,
    Yuliana Gu