cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mark973 Regular Visitor
Regular Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Create Subtotal in Matrix

Hi @Mark973 

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" } )
    )
)

11.png

 

 

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.

Mark973 Regular Visitor
Regular Visitor

Re: Create Subtotal in Matrix

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 3
Mark973 Regular Visitor
Regular Visitor

Re: Create Subtotal in Matrix

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%
Community Support Team
Community Support Team

Re: Create Subtotal in Matrix

Hi @Mark973 

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" } )
    )
)

11.png

 

 

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.

Mark973 Regular Visitor
Regular Visitor

Re: Create Subtotal in Matrix

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