2 Replies Latest reply on May 17, 2013 6:04 AM by h b

    display different averages in one sheet

    h b

      Hi,

      i have a db for all sales made

      it contains 1 row per sale made and has the fields "date", "document nr.", "product type", "branch", "revenue", "cost"

       

      Now i would like to display in one sheet the following information:

      in the rows branch + product -> ok

      in the columns

      -"revenue in last week" -> this is a problem because as long as i don't filter the whole data to display only last week it always shows me sum for the whole database

      -"Average revenue (per product per branch)" -> this measure works fine

      -"difference in average revenue of last month compared to average revenue of last year" -> also this measure i dont understand how to setup.

      -"difference in average revenue per product in one branch compared to average revenue for this product in the whole group". i.e. the average revenue one branch makes compared to the whole group -> also here i'm stuck

       

      All measure fields work on the whole of the data in my sheet, i dont understand how i can limit some calculation to certain data sets while make other calculations on the entire db..

       

      i guess it has something to do with add tabl ecalculation, but i cant figure where to start...

        • 1. Re: display different averages in one sheet
          Tracy Rodgers

          Hi H,

           

          A couple of calculated fields can be created to show your data for revenue in the last time periods:

           

          Sales in the Past Week:

          if [Date]>=dateadd('day', -7, today()) and [Date]<=today() then Sales end

           

          Sales in the Past Month:

          if [Date]>=dateadd('month', -1, today()) and [Date]<=today() then Sales end

           

          Sales in the Past Year:

          if [Date]>=dateadd('year', -1, today()) and [Date]<=today() then Sales end

           

          Then, you can create calculations based off of these once they are set up.

           

          For example, to find the total average of last month compared to last year, it might look similar to the following:

           

          total(avg([Sales in the Past Month]))-total(avg([Sales in the Past Year]))

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: display different averages in one sheet
            h b

            this looks, promising, will test this weekend. What about comparing one branches average revenue/sale to the overall revenue/sale of the whole group?