4 Replies Latest reply on Aug 30, 2016 10:50 AM by Brad Slaughter

# KPI based upon comparison within single dimension

I have a datatset with a market dimension.  I want to compare the growth of two markets in the dimension and create a KPI. So, arrow up if market A is greater than market B.

 mkt a b 2.3 -1.5
• ###### 1. Re: KPI based upon comparison within single dimension

It's tough to give an exact solution without more context, but here's a shot:

IF {FIXED [Dimension] : SUM(IF [mkt]="a" THEN [Measure] END)} > {FIXED [Dimension] : SUM(IF [mkt]="b" THEN [Measure] END)}

THEN "↑"

ELSE "↓"

END

1 of 1 people found this helpful
• ###### 2. Re: KPI based upon comparison within single dimension

Sorry, I did not have a masked dataset available.  I have attached a workbook to my initial post.  I get an can't mix aggregates message with your fix.

• ###### 3. Re: KPI based upon comparison within single dimension

OK I got something. I went ahead and added one more dimension and a few rows with random values into your sample data to help frame the solution.

1 of 1 people found this helpful
• ###### 4. Re: KPI based upon comparison within single dimension

Thanks for the help, Benjamin!   I made a slight tweak to your solution.  I actually didn't need to do a fixed calculation because of how I was going to set up the final viz.

IF (SUM(IF [MKT]="MKT A" THEN [Dollars] END) -
SUM(IF [MKT]="MKT A" THEN [Dollars YAG] END))/
SUM(IF [MKT]="MKT A" THEN [Dollars YAG] END)
>
(SUM(IF [MKT]="MKT B" THEN [Dollars] END) -
SUM(IF [MKT]="MKT B" THEN [Dollars YAG] END))/
SUM(IF [MKT]="MKT B" THEN [Dollars YAG] END)

THEN
"FASTER"
ELSE
"SLOWER"
END