6 Replies Latest reply: Jun 7, 2016 9:38 AM by annick whitfield

# KPI : Link and compare 2 measures with different dimensions

Hello,

I would like to compare 2 measures "Sales Amount"  and "Target Sales Amount"

Sale:
[
SaleID, SaleAmount, SaleYear, SaleMonth, SaleCity, SaleDay
101,     100,                2016,       1,                PAR,       10
102,      10,                 2016,       1,                LON,       11
103,      150,               2016,      3,                 HEL,        9
104,      210,               2016,      4,                 FRA,       2
105,      40,                 2016,      4,                 PAR,     13
106,      345,               2016,      8,                 LON,     21
107,      35,                 2016,      8,                 FRA,     25
];

Target:
[
TargetYear, TargetMonth, TargetCity, TargetAmount
2016,  1,    PAR,  20
2016,  1,    LON,  30
2016,  2,    PAR,  10
2016,  2,    LON,  20
2016,  5,    HEL,  15
2016,  6,    PAR,  10
2016,  6,    LON,  20
2016,  7,    PAR,  15
2016,  8,    PAR,  20
2016,  9,    HEL,  15
2016,  10,    HEL,  5
2016,  11,    HEL,  0
2016,  12,    PAR,  15
2016,  12,    LON,  25

];

In the UI I have the following KPI:

Sale Amount = Sum(SaleAmount)

I would like to add a KPI which is the sum(TargetAmount) for TargetMonth = SaleMonth  and TargetYear = SaleYear.(i.e. SaleMonth and TargetYear as selected above).

Is there a way to achieve this in the UI without changing the load script ? if not possible, what would be the best way, knowing that the filters might evolve in the future i.e. : SaleCity, SaleDay etc...

Many thanks.

Annick

• ###### Re: KPI : Link and compare 2 measures with different dimensions

Hi Annick,

Try with this expression:

=Sum({<TargetMonth=p(SaleMonth),TargetYear=p(SaleYear)>}TargetAmount)

Regards

Miguel del Valle

• ###### Re: KPI : Link and compare 2 measures with different dimensions

Hi Annick

without changing the load script here is a little approch to test and adapt

KPI target amount use a set analysis as mesure like this :

Sum({\$<TargetMonth={\$(=num(SaleMonth))}>}TargetAmount)

it is summing the target amount depending of targetmouth when this target month is egual to SaleMonth

the same mesure adding year dimension :

Sum({\$<TargetMonth={\$(=num(SaleMonth))},TargetYear={\$(=num(SaleYear))}>}TargetAmount)

and so on , if you want to drill down with city and day

hope it helps

regards

bruno

• ###### Re: KPI : Link and compare 2 measures with different dimensions

Thanks a lot, it works almost perfectly. I still have one requirement: is it possible to adapt this formula so that if no SaleMonth has been selected, the sum of all months is calculated? Many thanks. Annick

• ###### Re: KPI : Link and compare 2 measures with different dimensions

hi

in that case , it seems that the p formula provided by miguel should respond to your demand and is easier to use.

• ###### Re: KPI : Link and compare 2 measures with different dimensions

Regards

Miguel del Valle

• ###### Re: KPI : Link and compare 2 measures with different dimensions

Hello Miguel,

I have marked your answer as correct since it gives correct result when SaleMonth is selected. However when no SaleMonth is selected, the expression does not seem to work. I will open a separate question for this. Thanks a lot for your help.

Annick