7 Replies Latest reply on Oct 9, 2013 3:07 AM by Sandra Coen

    pro rata

    Sandra Coen

      hello - I need to create a pro-rata. I have a number of impressions by day, but a total of cost for the month. I want to use the pro rata of my daily impression to have a daily cost. Does anyone know how to do this? thanks Sandra

        • 1. Re: pro rata
          Shawn Wallwork

          Sandra welcome to the forums! You really need to give us a few more details. But if you put your date field on the row or column shelf set to MONTH and then create a calculation like this:

           

          SUM([Impressions])/SUM([Cost])

           

          And you this on the row or column shelf, you should get a bar chart showing your cost per impression aggregated to month. This of course depends on how your data is laid out. Is it coming from the same data source or are costs coming from a different data source? What's the linking field for those data sources.

           

          More info, and you'll get more/better help.

           

          Cheers,

           

          --Shawn

          • 2. Re: pro rata
            Sandra Coen

            thanks for replying

            do you know how I can attached an excel doc? it shows what I need to do

            thanks!

            Sandra

            • 3. Re: pro rata
              Sandra Coen

              will try what you wrote and let you know thanks

              • 4. Re: Re: pro rata
                Cristian Vasile

                Sandra,

                 

                click on top right corner and enable advance editor, and then on the bottom right corner you could click on Attach.

                 

                Regards,

                Cristian.

                • 5. Re: Re: Re: pro rata
                  Sandra Coen

                  great thanks!

                  so I have a Total cost of the month

                  and I have my impressions split by day

                   

                  I have done a factor for each day : impresion of the day/total impression of the month

                   

                  I want to use this daily factor to split my spend by day

                   

                   

                  let me know if not clear...

                  thanks

                  Sandra

                  • 6. Re: Re: Re: Re: pro rata
                    Shawn Wallwork

                    Sandra, it sounds like your data is coming from two different sources. One for the impressions, clicks, etc. aggregated daily and another one that holds your spend or costs aggregated by month. I assumed this to be the case in the attached workbook. The only tricky bit (apart from table calculations in general) is that you need to create this calculation in each data source:

                     

                    DATENAME('month', [Date] )

                     

                    And use it as your linking field, NOT the date field. In the secondary data source note the broken (grey) link and the functioning (orange) link, and which is which. After that it is just a matter of setting your compute using fields correctly in the table calculations.

                     

                    If you have more questions, just ask.

                     

                    --Shawn

                    • 7. Re: Re: Re: Re: pro rata
                      Sandra Coen

                      Thanks a lot

                      I think the best then is to do this in Excel not to mess up Tableau too much

                      I keep this though it might be useful.

                      Thanks

                      Sandra