How to generate a table with vertical metrics and the columns being this month, last month, and month over month change

Q&A
,
1

I am trying to generate a table where I have my metrics going down the left side of the table and the dates (monthly) going across the top plus a Month over Month % difference also being a column.

I am able to use the “Swap rows and columns” button on the table to get the metrics and dates where I want them, but I’m unsure how to add the MoM % difference.

The goal is to get it to look something like this:

image
image319×163 3.37 KB

Is this doable in QuickSight? Should I be using a pivot table instead? Should I be pre-calculating this in a dataset instead?

Any recommendations and advice is appreciated.

2

Hi @hcorbett :slight_smile:
have you already taken a look at the periodOverPeriod functions?

Like, if you want percentage, it should be: periodOverPeriodPercentDifference - Amazon QuickSight

Andrea

3

I have but I’m not sure how to add it as a vertical column since it would need to go in the Value’s section, not the “Group By” section which would make it a column instead of row (once I switched the rows and columns)

4

Given that you have two different fields providing data from This_Month and Past_Month, you can probably go with a calculated field MoM like:

(  sum({Last_Month})  - sum({This_Month}) ) / sum({Last_Month})

Or changing Last with This with respect to what you want to have as base for your percentage calculation.

image
image958×780 37.2 KB

Swapping Row and Columns keep the data the same in this case

image
image2728×402 26.2 KB

Let me know if this helps!

Andrea

5

That is what I did but I realized it would not work because my metrics need two different formats. Some of my metrics are numbers and some of my metrics are percentages. Since “This Month” and “Previous” are in the values section of the table, I can only set the format to Number OR Percent. Not both.

I do not think I’ll be able to do what I’m wanting due to the different formatting needs.

6

Hi @hcorbett

Why don’t you have three calculated fields This Month, Last Month and Mom% diff and place them in the values section in that order.

I don’t see any easy way to format data if these came as columns from your table. MoM% should anyway be a calculated field in the analysis as the result could change based on the level of detail you are looking at.

regards,
Giri

7

Hi Giri,

It’s because my metrics are a mix of numbers and percentages. And then the MoM is also a %. Because of that mix of formats, I haven’t been able to find a way to display the data in a straight, forward way

8

Hi @hcorbett,

It’s been awhile since we last heard from you on this thread, are you still encountering the same issues or were you able to find a work around for your scenario?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

9

Yes, I am still having the same issue. However my work around was aligning KPI’s in a grid-format to mimic a table.

10

Hi @hcorbett,

Agreed that work around may be your best option in this scenario unless there are additional features implemented on the flexibility of tables and calculations.