How to get difference based on dimension values

Question & Answer
, , ,
1

Hi All,

Is there any way to calculate the difference of calculation for 2 values coming from on dimension.
For example:

Screenshot 2023-04-07 at 8.14.34 PM
Screenshot 2023-04-07 at 8.14.34 PM1143×244 22 KB

Here we have Country Dimension which has Country values like US, India etc.
Measure used in table is Defect Per Million which is calculated by :
Sum(Defected Unit)/sum(Total Unit)* 1000000

We want to have another value Difference in Dimension which gives difference betweenDPMO of US and India

2

You would use a lag or lead function.

3

Thank you Max for your response !!

But Lead/ Lag works on Measures whereas this calculation needs to be done based on Dimension values.

we can write something like
if country=‘Difference’ then
(if Country= ‘US’ then ‘Defect Per Million’ - if Country= ‘India’ then ‘Defect Per Million’)
else
‘Defect Per Million’

Difference can be added as Dummy value to Country column

4

Hi @anshulsri
You can use an ifelse() to achieve this.

sum(ifelse({Country} = 'US', DPMO, 0)) - sum(ifelse({Country} = 'India', DPMO, 0))

You may need to add the ifelse() at your base calculation so you are not nesting sums

1 Like
5

Just follow the example using the difference function for aggregates Difference - Amazon QuickSight

6

If your just looking for a way to do the calculation on two dimensions, as Douglas mentioned ifelse block would help achieve your requirement.

Quicksight has a key performance indicator (KPI) visual to compare values, please go through this link to learn more.

Pls mark the question as Solved if we have addressed your initial query. Appreciate helping the community with your feedback.

1 Like
7

Hi @anshulsri ! We hope either @bergqdou or @knamburi solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking their answer as a “Solution.”

Regards,
Karthik

1 Like
8

Thank you for your response @bergqdou .
But Difference should be included in the Dimension Country which could have multiple values .

Another example of same scenario is:
I have data for Period as Wk_15, Wk_16, Wk_17, W_18
Here, I need to have WoW i.e. sum(ifelse({period} = ‘Wk_18’, DPMO, 0)) -sum(ifelse({period} = ‘Wk_17’, DPMO, 0))
and WoW should be coming in Period Dimension

9

Hi @anshulsri,
Following up on the questions you’d raised in this thread. Multiple different options had been suggested, each taking a slightly different approach. Did you finally manage to solve your case and want to share your approach with the community?

If it’s not yet solved, could you please provide a more detailed description/screenshots of how your input is looking like and how your output is expected to look like? If the cardinality of the dimension that you want to compare is higher than 2, I’d like to understand how/what you want to compare with each other (e.g. if the country column contains more than only the values “US” and “India”, what differences do you want to show where?).

10

Hi @anshulsri

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

11

Hi @anshulsri

Since we have not received a response from you on this question, we are going to archive the question. If you still need this question answered, please create a new question topic