Numerous Subtotals Formula

Super User

6 years ago

Hi mibu,

Have been sick for the last days. Will check this and get back to you ASAP.

Sorry for the delay.

Regards,
MFelix

mibu

Microsoft Employee

6 years ago

Sorry the hear that MFelix

Thanks for the udpate! Look forward to hearing back from you!

Best,

mibu

Nathaniel_C

MFelix

Super User

6 years ago

Hi mibu,

Make the following change in the table CAT:

CATID

A	1
B	1
A+B	2
C	3
D	3
E	3
C+D+E	4
F	5
A+B+C+D+E+F	6
G	7
H	7
G+H	8

Now add the following measure to your model

CALCULATION_ =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( CATID[ID] ) = 1; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 2; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "A"; "B" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 3; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 4; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "C"; "D"; "E" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 5; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 6; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "A"; "B"; "C"; "D"; "E"; "F" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 7; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 8; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "G"; "H" } )
    )
)

This can probably be simplified, but since I took so long to answer you I think is better to share this result.

I will try to improve this later on but this is working currently.

Regards,

MFelix

Forum Discussion