2 Replies Latest reply on Dec 1, 2016 9:44 AM by P B

    LOD - Making calculations on dimensions that are not included in shelf

    P B

      Hi Guys,

       

      I'm working on a dashboard focused on Sales Information. Two things worth mentioning:

      1. Each sale has an indicator whether is was returned or not. In the overview we use general Sales without Return / All Sales KPI.

      2. Each Dimension has its own target, which means that we are calculating the difference between sales and dimension's target.

       

      Detailed view:

      Details.png

      [Target] is a target for Genre/Game combination, where [_Target] is a target for higher level dimension, in this case: Province.

       

      As you can see we have a breakdown for 3 days. First measure indicated the ratio of Sales without Return / All Sales, second measure is just a Number of Records representing all sales (Returned and not Returned) and a third one indicates whether Sales without Return / All Sales meets the [Target].

      What i would like to do, is to SUM all 1 and 0 across Ganre/Game dimensions and calculate the difference between this value and [_Target].

      So for above example we can see that [_Target] for province Biorno is 67% (0,67) and for Garna 77% (0,77).

      So the calculation would look like this:

      1. Biorno Day 1: ((1 + 0) / 2) - 0,67 = -0,17

      2. Biorno Day 2: ((0 + 0) / 2) - 0,67 = -0,67

      3. Biorno Day 3: ((0 + 0) / 2) - 0,67 = -0,67

      4. Garna Day 1: ((1 + 0) / 2) - 0,77 = -0,27

      5. Garna Day 2: ((1 + 1) / 2) - 0,77 = 0,23

      6. Garna Day 3: ((0 + 1) / 2) - 0,77 = -0,27

       

      2 represents number of records in this 'column' within province.

       

      Desired outcome:

      High_Level.png

       

      Is that possible?

      Thanks in advance.