How can I add a KPI calculation based on 2 other KPIs?

Question & Answer
, , , , , ,
1

My End Goal: Get a KPI visual that showing the percentage change based on a metric (incident number) for this month-to-date vs. last-month-to-date, [eg. Today is 12/17, I want a number from 12/1 to12/17, and another number from 11/1 to 11/17, and compare its difference. If there are 40 incidents since this month, and 80 from 11/1 to 11/17, then the KPI showing “-50%”].

What I did: Since the built-in “filter” can do “this month”, but not “previous month beginning till same date last month”, I have to create 3 calculated fields (MTD, LMTD, PercentageChange) to pull the number I want from the dataset. Like this:

IMG_0125.HEIC
IMG_0125.HEIC3690×4077 3.62 MB

And then add the “PerChange” as the new KPI.

Problem with that method:

  1. The KPI is not showing what I want (wrong percentage)
  2. The “PerChange” is assuming MTD is greater than LMTD, which is not always the case.

Question: How show I solve these? Or in general, is there a way to add a new KPI visual, calculating based on 2 other KPIs in QuickSight? Or write a SQL code?

Thanks!!

2

Hi @Max111
did you check all three measure if the values are as expected?
Formula looks right to me.
BR

1 Like
3

Hi @ErikG, the MTD and LMTD are right, but PerChange looks wired when I pull it into a KPI box. Is there any way I can ignore PerChange and just create a new KPI visual based on the 2 other KPIs I already created in Quicksight? Thanks!

4

What if you put it into table visual. Thats always the better visual to check “wired” things. :wink:

5

Hello @Max111 !

Was @ErikG 's suggestion helpful, and if so could you mark their comment as a solution to help the community?

It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 3 business days this topic will be archived.