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,

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

Sorry for the delay.

Regards,
MFelix
  • mibu's avatar
    mibu
    Icon for Microsoft Employee rankMicrosoft 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's avatar
      MFelix
      Icon for Super User rankSuper 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

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

        Hi MFelix

        Apologies for delay in getting back to you!

        I am trying to recreate your solution.

        I copied your formula straight from Forum and am seeing the following error message.

        Thoughts?  Thanks!

        mibu