Forum Discussion

mibu's avatar
mibu
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Numerous 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 / ...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi 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