1 Reply Latest reply on Jan 25, 2017 2:22 PM by Jamieson Christian

    LOD expression to compare sales by sub-category to avg by category

    jack.glavosek

      I'm trying to create an LOD expression that gives me the average sales of the sub-categories within a category.  Then, I want to create a calculations that compare the sales by sub-category to the average.

      I'm having trouble with the LOD expression:({fixed Category,[Order Date]:sum(Sales)})

       

      I'm trying to create a table that looks like this:

       

      CategorySub-CategorySalesAverage for CategorySales - Avg
      FurnitureBookcases$20,037$39,298-$19,262
      Chairs$77,242$39,298$37,943
      Furnishings$13,826$39,298-$25,472
      Tables$46,088$39,298$6,790
      Office SuppliesAppliances$15,314$16,864-$1,550
      Art$6,058$16,864-$10,806
      Binders$43,488$16,864$26,624
      Envelopes$3,856$16,864-$13,008
      Fasteners$661$16,864-$16,203
      Labels$2,841$16,864-$14,023
      Paper$14,835$16,864-$2,029
      Storage$50,329$16,864$33,465
      Supplies$14,394$16,864-$2,470
      TechnologyAccessories$25,014$43,820-$18,805
      Copiers$10,850$43,820-$32,970
      Machines$62,023$43,820$18,204
      Phones$77,391$43,820$33,571
        • 1. Re: LOD expression to compare sales by sub-category to avg by category
          Jamieson Christian

          Jack,

           

          Very well crafted question! I like how you provided a sample of what you expect to see, which is very helpful in validating a solution.

           

          I've attached a workbook, with two solutions. It's in version 10.1.3 so you may not be able to open it. I'll describe what I did below:

           

          METHOD 1 — Fixed LOD expression

           

          1. Change [Avg by Category] like so. (Notice I switched to INCLUDE/EXCLUDE expressions, but it can be written as a FIXED expression if you want to stick with FIXED.)

           

          2. Changed [Actual - Avg] like so:

           

          3. Dragged [Avg by Category] back onto the Measure Values shelf to switch it to ATTR(). Voila.

           

           

          METHOD 2 — Table calculations

           

          1. Change [Avg by Category] as shown. Note the "Default Table Calculation" that was configured ("…computed along Sub-Category").

           

          2. Voila.

           

          Hope this helps!

          1 of 1 people found this helpful