3 Replies Latest reply: Jul 2, 2013 11:26 AM by Nicole Smith RSS

    Dimension Limitation Top30

    Simon Koenen

      Hi,

       

      I have created a chart with 3 dimensions "Country", "Brand" and "Product segment" and the following expression:

       

      =SUM({<

                [Measures (Key)]={'Variable gross margin'},

                [Version (Key)]={'$(=vToggle_Version_Key1)'},

                [Type of data (Key)]={'1'},

                [Business area (Key)]={'M'}

      >}Values)-SUM({<

                [Measures (Key)]={'Variable gross margin'},

                [Version (Key)]={'$(=vToggle_Version_Key2)'},

                [Type of data (Key)]={'1'},

                [Business area (Key)]={'M'}

      >}Values)

       

      To this point no problem.

       

      But now I would like to show only the top30 and the rest accumulated to "others".

       

      With one dimension this is no problem but with three it does not work.

       

      Furthermore I have a column showing the percentage of total using this calculation:

       

      =column(1)/vCompensingEffectsSum*100

       

      using the variable vCompensingEffectsSum

       

      =SUM(

                AGGR(

                          SUM({<

                                    [Measures (Key)]={'Variable gross margin'},

                                    [Version (Key)]={'$(=vToggle_Version_Key1)'},

                                    [Type of data (Key)]={'1'},

                                    [Business area (Key)]={'M'}

                          >}Values)-SUM({<

                                    [Measures (Key)]={'Variable gross margin'},

                                    [Version (Key)]={'$(=vToggle_Version_Key2)'},

                                    [Type of data (Key)]={'1'},

                                    [Business area (Key)]={'M'}

                          >}Values)

                ,

                          [Country group]

                ,

                          [Brand division]

                ,

                          [Product segment]

                )

      )

       

      And I need a third column with the accumulated %-calculation.

       

       

      Any suggestions?