Forum Discussion
Subgroups and categories in same table
I like to create a matrix table that show both my categories and a partial subgroup in the same table. My expected output is like this:
| Category | Sum |
| A | 25 |
| B | 28 |
| C | 33 |
| Subcategory (A+B+C) | 86 |
| D | 34 |
| Grand Total | 120 |
Dataset is like this:
| Category | Value |
| A | 12 |
| A | 23 |
| B | 13 |
| B | 15 |
| C | 16 |
| C | 17 |
| D | 15 |
| D | 19 |
Hi danguy2099 ,
The Value of A's is 2: 23+12=35
Here are the steps you can follow：
1. Create calculated table.
Table_True = var _summarize1= SUMMARIZE( 'Table', "Category","Subcategory(A+B+C)", "Sum",SUM('Table'[Value])) var _table1= UNION( 'Table',_summarize1) var _summarize2= SUMMARIZE( _table1, "Category","Grand Total", "Sum",SUMX(_table1,[Value])) var _table2= UNION( _table1,_summarize2) return _table22. Create calculated column.
Value2 = SWITCH( TRUE(), 'Table_True'[Category] = "Subcategory(A+B+C)", CALCULATE(SUM('Table_True'[Value]),FILTER(ALL('Table_True'),'Table_True'[Category] in {"A","B","C"} )), 'Table_True'[Category] = "Grand Total", CALCULATE(SUM('Table_True'[Value]),FILTER(ALL('Table_True'),NOT( 'Table_True'[Category]) in { "Subcategory(A+B+C)","Grand Total"} )), 'Table_True'[Value] )3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi,
Pls try this. Create a maual table by enter data option as below and make a relationship with your table. Then drag columns from newly created table and value column.Hi
Thank you for possible solution. I'm aware over this option, but I would like to have it on "one" line in a matrix if you know what I mean. I was thinking a solution based on a virtual table in DAX. Do you know how I would achieve that?
Hi danguy2099 ,
The Value of A's is 2: 23+12=35
Here are the steps you can follow：
1. Create calculated table.
Table_True = var _summarize1= SUMMARIZE( 'Table', "Category","Subcategory(A+B+C)", "Sum",SUM('Table'[Value])) var _table1= UNION( 'Table',_summarize1) var _summarize2= SUMMARIZE( _table1, "Category","Grand Total", "Sum",SUMX(_table1,[Value])) var _table2= UNION( _table1,_summarize2) return _table22. Create calculated column.
Value2 = SWITCH( TRUE(), 'Table_True'[Category] = "Subcategory(A+B+C)", CALCULATE(SUM('Table_True'[Value]),FILTER(ALL('Table_True'),'Table_True'[Category] in {"A","B","C"} )), 'Table_True'[Category] = "Grand Total", CALCULATE(SUM('Table_True'[Value]),FILTER(ALL('Table_True'),NOT( 'Table_True'[Category]) in { "Subcategory(A+B+C)","Grand Total"} )), 'Table_True'[Value] )3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Worked like a charm. Thank you so much!
