Forum Discussion
mibu
Microsoft Employee
Microsoft EmployeeNumerous Subtotals Formula
Hey Everybody! I have the following data: Type / Units A / 100 B / 200 C / 300 Is there a formula that will allow me to show the following in a Matrix: Type/Units A / 100 B / 200 C / ...
MFelix
Super User
Super UserHi mibu ,
Create a new table with the following format
CAT ID
| A | 1 |
| B | 2 |
| C | 3 |
| A+B | 4 |
| A+B+C | 5 |
Now add the following measure to your model.
CALCULATION =
SWITCH (
TRUE ();
MAX ( CAT[ID] ) < 4; CALCULATE ( SUM ( 'Table'[Units] ); 'Table'[Type] = VALUES ( CAT[CAT] ) );
MAX ( CAT[ID] ) = 4; CALCULATE (
SUM ( 'Table'[Units] );
FILTER ( ALL ( 'Table'[Type] ); 'Table'[Type] IN { "a"; "B" } )
);
CALCULATE ( SUM ( 'Table'[Units] ); ALL ( 'Table'[Type] ) )
)
Then use the previous table information and the measure to create your matrix.
Regards,
MFelix
- mibu
Hi MFelix
Is it possible to approach the problem in this way:
Create a new table as follows (table and .pbix file ABCTEST uploaded to dropbox here
And then come up with a Calculation/Measure/Formula that allows us to show the following in a single MATRIX:
A through H… each in its own Row (alphabetically)…
A+B… in its own Row (below row B)…
C+D+E … in its own Row (below row E)…
A+B+C+D+E... in its own Row (below C+D+E Subtotal row)...
A+B+C+D+E+F... in its own Row (below row F)...
G+H… in its own Row (below row H)…
Thanks!
Mibu
