Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I like to create a matrix table that show both my categories and a partial subgroup in one column 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 |
Solved! Go to Solution.
Hi @danguy2099
Not so pretty but it works. Start by creatinga filter table (Categories)
Categories =
SELECTCOLUMNS (
{ ( "A", 1 ), ( "B", 2 ), ( "C", 3 ), ( "Subcategory (A+B+C)", 4 ), ( "D", 5 ) },
"Category", [Value1],
"Index", [Value2]
)
SUM =
IF (
ISEMPTY ( 'Dataset' ),
CALCULATE (
SUM ( 'Dataset'[Value] ),
'Dataset'[Category] IN { "A", "B", "C" },
ALL ( 'Dataset' )
),
SUM ( 'Dataset'[Value] )
)
Works as intended. Thanks
Hi @danguy2099
Not so pretty but it works. Start by creatinga filter table (Categories)
Categories =
SELECTCOLUMNS (
{ ( "A", 1 ), ( "B", 2 ), ( "C", 3 ), ( "Subcategory (A+B+C)", 4 ), ( "D", 5 ) },
"Category", [Value1],
"Index", [Value2]
)
SUM =
IF (
ISEMPTY ( 'Dataset' ),
CALCULATE (
SUM ( 'Dataset'[Value] ),
'Dataset'[Category] IN { "A", "B", "C" },
ALL ( 'Dataset' )
),
SUM ( 'Dataset'[Value] )
)
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |