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.





        • 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.



          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])




            [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 LY])-1




            [Margin % AGG]

            SUM([Margin £])




            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

            1 of 1 people found this helpful