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

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: [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: Is that possible?

• 1. Re: LOD - Making calculations on dimensions that are not included in shelf

AVG(

{INCLUDE [Game (Sales)] :

IF SUM(IF [Return] = 0 THEN 1 ELSE 0 END)/SUM(1) >= MIN([Target])

THEN 1 ELSE 0 END

}

)

- ATTR([_Target])

The key idea here was that you want part of your calculation (whether or not the no return sales target was met) computed by game, whereas the rest you want the calculation rolled up to the level of your viz.

• 2. Re: LOD - Making calculations on dimensions that are not included in shelf

That calculation did the trick. Thanks a lot Stephen!