1 Reply Latest reply on Apr 12, 2018 3:09 PM by Chris McClellan

    Percent of Dimension

    Tim Zalk

      Not sure the right way to ask this, so I apologize if it's an inaccurate title.

       

      I'm looking to compute the percentage of units sold in a certain sales program (incentives) out of the total sales. For total sales I receive a weekly summary spreadsheet, and units sold through the inventive program I have individual database records. Ideally, I would connect to the MS SQL database for the incentive sales, and connect to a network drive for the spreadsheet with total sales summary. I was having trouble with the blending, so I ended up creating a new spreadsheet with a summary of the incentive sales and total sales. I have the result I want (see below), but don't want to have to update the spreadsheet every time I need to share the chart again.

       

      1. Is there a way to do this by connecting to the separate data sources?
      2. What's the best way to set up the calculated field / table calculation to find the percentage? I kept getting the inverse initially, so i added 1 / x to the calculation and that seemed to get the correct percentage.

      Capture31.PNG

        • 1. Re: Percent of Dimension
          Chris McClellan

          I'm not 100% sure if this would work, but try a cross-database join between SQL Server and Excel (probably using SQL Server as the primary data source).

           

          Once you can pull the sales data in properly (not the manual merge that you're trying to avoid), the next step would be create a calculated field (probably using an LOD) to do the percentage calculation.

           

          Try that and reply here if you need more help