Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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".
Status | Sales | Profit % |
E | 1000 | 10.0% |
R | 200 | 8.0% |
N | 200 | 7.0% |
R+N | 300 | 7.5% |
The usual Matrix total gives E+R+N, but I only want to subtotal of R+N.
Thank you, Mark
Solved! Go to Solution.
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.
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])
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.
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])
Please note that there was a slight error in the above table. The R+N sales total should be $400 (not $300).
Corrected table:
Status | Sales | Profit % |
E | 1000 | 10.0% |
R | 200 | 8.0% |
N | 200 | 7.0% |
R+N | 400 | 7.5% |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |