Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create Subtotal in Matrix

Hi All -

 

What is the best way to create a subtotal in a Matrix?

 

The final table should look like the following.  Basically, the question is how to get the subtotal "R+N". 

 

StatusSalesProfit %
E100010.0%
R2008.0%
N2007.0%
R+N3007.5%

 

The usual Matrix total gives E+R+N, but I only want to subtotal of R+N.

 

Thank you, Mark

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    If your data structure as below

    Status Sales Profit %
    E 1000 10.0%
    R 200 8.0%
    N 200 7.0%


    create measures

    Measure_Sales =IF (
    HASONEVALUE ( Sheet10[Status] ),
    SUM ( Sheet10[Sales] ),
    CALCULATE (
        SUM ( Sheet10[Sales] ),
        FILTER ( ALL ( Sheet10 ), [Status] IN { "R", "N" } )
    )
)


Measure_Profit % =
IF (
    HASONEVALUE ( Sheet10[Status] ),
    SUM ( Sheet10[Profit %] ),
    CALCULATE (
        AVERAGE ( Sheet10[Profit %] ),
        FILTER ( ALL ( Sheet10 ), [Status] IN { "R", "N" } )
    )
)

     

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    7 years ago

    Thank you Maggie ... that worked!

     

    One thing that I can't get to work is to rename the "Total" in the matrix to "N + R".

     

    Also I included some code below (for the next person I suppose) to shows how I applied the formula you provided ...

     

    ECR_RN_Subtotal = 
IF (
    HASONEVALUE ( Sheet1[Contract_Status]),
    [WECR],
    CALCULATE (
        [WECR],
        FILTER ( ALL ( Sheet1 ), [Contract_Status] IN { "Renewal", "New" } )
    )
)

WECR = DIVIDE(SUMX(Sheet1, Key_Measures[EP] * Key_Measures[ECR]), Key_Measures[EP])

EP = SUM(Sheet1[EP])

ERC = SUM(Sheet1[ERC])

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    If your data structure as below

    Status Sales Profit %
    E 1000 10.0%
    R 200 8.0%
    N 200 7.0%


    create measures

    Measure_Sales =IF (
    HASONEVALUE ( Sheet10[Status] ),
    SUM ( Sheet10[Sales] ),
    CALCULATE (
        SUM ( Sheet10[Sales] ),
        FILTER ( ALL ( Sheet10 ), [Status] IN { "R", "N" } )
    )
)


Measure_Profit % =
IF (
    HASONEVALUE ( Sheet10[Status] ),
    SUM ( Sheet10[Profit %] ),
    CALCULATE (
        AVERAGE ( Sheet10[Profit %] ),
        FILTER ( ALL ( Sheet10 ), [Status] IN { "R", "N" } )
    )
)

     

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you Maggie ... that worked!

       

      One thing that I can't get to work is to rename the "Total" in the matrix to "N + R".

       

      Also I included some code below (for the next person I suppose) to shows how I applied the formula you provided ...

       

      ECR_RN_Subtotal = 
IF (
    HASONEVALUE ( Sheet1[Contract_Status]),
    [WECR],
    CALCULATE (
        [WECR],
        FILTER ( ALL ( Sheet1 ), [Contract_Status] IN { "Renewal", "New" } )
    )
)

WECR = DIVIDE(SUMX(Sheet1, Key_Measures[EP] * Key_Measures[ECR]), Key_Measures[EP])

EP = SUM(Sheet1[EP])

ERC = SUM(Sheet1[ERC])
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Please note that there was a slight error in the above table.  The R+N sales total should be $400 (not $300).

     

    Corrected table:

     

    StatusSalesProfit %
    E100010.0%
    R2008.0%
    N2007.0%
    R+N4007.5%