2 Replies Latest reply on Aug 20, 2018 1:16 PM by Simon Runc

    Grouping by a dimension

    Albert Dorfman

      Hey everyone,

       

      I have data formatted like this:

       

      And I'm trying to get a graph that looks like this at a supplier level (i.e. each dot represents one supplier)

       

      My major issue is figuring out how to group the data at a supplier level, so that each dot has the information I want, and also represents one individual supplier.

       

      Any help would be appreciated.

       

      Thanks!

       

      Albert

        • 1. Re: Grouping by a dimension
          Deepak Rai

          Hi Albert,

          It is always good to have a  packaged workbook attached, But Do This:

          Create 2 Calcs
          {FIXED Supplier:SUM(Margin)}and

          {FIXED Supplier:SUM(Sales Growth)}

           

          Drag Sales Growth Calc to Columns and Margin Calc to Rows and Supplier to Color.

          Thanks

          Deepak

          If It Doesn't work, Pl attach some workbook or fake data to help you.

          • 2. Re: Grouping by a dimension
            Simon Runc

            Hi Albert,

             

            So the issue you have here (assuming you want to weight the ratios of Growth and Margin) is that you have them as %ages, so we are unable to aggregate them to a level above the level we find them in the data. So we need to turn them back to the values that make up the rations (namely Margin £ and Sales LY).

             

            We can do this like so

            [Sales LY]

            [Sales]/(1+[Sales Growth])

             

            and

             

            [Margin £]

            [Sales]*[Margin]

             

            and now we can create aggregated version of your fields, which will aggregate (SUMing both sides) to any level

             

            [Sales Growth % AGG]

            SUM([Sales])

            /

            SUM([Sales LY])-1

             

            and

             

            [Margin % AGG]

            SUM([Margin £])

            /

            SUM([Sales])

             

            and now we can just use these fields (and the Supplier level of detail) to create the scatter plot.

             

            Hope that helps and makes sense