Two value in One

Q&A
,
1

Hello Team.

Could you please help to convert two values into one?
For example, I have two values visa 48% and mastercard 45%, but I want to combine them into one value, for example CARDS 93%.

Thank you.

image

2

Hi @weird_guy,
Could you provide us with some additional information regarding this with a screenshot of your overall visual (including fields being used).
What field is producing the ‘visa’ and ‘mastercard’ values? How many other values are there (and do any others need to be grouped as well)?

3

Hello @Brett
Thank you for your response.

I have the field “payment_methods” with values visa, mastercard and etc.
And “Orders” just a quantity.

image
image248×236 7.74 KB

4

Hi @weird_guy ,

Can you use this calculation for your scenarios.

image
image861×510 11.1 KB

Thanks & Regards
Biswajit Dash

2 Likes
5

@Biswajit_1993 Hello. Please clarify, is it two ways or just one calculation?

6

If that does not work you can also try this as well.

To convert the two values into one in Quicksight, you can use the following steps:

Create a calculated field to combine the values for "visa" and "mastercard":

CASE
  WHEN payment_methods = 'visa' THEN Orders
  WHEN payment_methods = 'mastercard' THEN Orders
  ELSE 0
END

This will create a new field that sums the orders for "visa" and "mastercard".

Create another calculated field to calculate the percentage of the combined value:

ROUND(SUM(CASE
  WHEN payment_methods = 'visa' THEN Orders
  WHEN payment_methods = 'mastercard' THEN Orders
  ELSE 0
END) / SUM(Orders) * 100, 2)

This will calculate the percentage of the combined "visa" and "mastercard" orders.

Finally, you can rename the second calculated field to "CARDS" to get the desired output:

ROUND(SUM(CASE
  WHEN payment_methods = 'visa' THEN Orders
  WHEN payment_methods = 'mastercard' THEN Orders
  ELSE 0
END) / SUM(Orders) * 100, 2) AS CARDS

The final result will be a single value "CARDS 93%" that combines the "visa" and "mastercard" percentages.

You can add this calculated field to your Quicksight analysis and use it as needed.

3 Likes
7

Thanks @murili for the detailed information.

Thanks & Regards
Biswajit Dash

8

Hi @weird_guy, both are two different calculations, you can use either one.

Thanks & Regards
Biswajit Dash

9

@murili Hello.
Is it correct syntaxis?

image
image458×278 4.11 KB

10

@Biswajit_1993 Hello
Please specify how to remove “Others” condition?
I don’t want to mark as “others” another methods, cards only.
Thank you.

image
image329×203 5.21 KB

11

Hi @weird_guy ,

There are two ways you can do this,

One by using a new calculated field as

image
image1066×331 10.7 KB

Second one is you can exclude the “Others” directly from the visual level by rt click and click on exclude.

Thanks & Regards
Biswajit Dash

1 Like
12

@Biswajit_1993 Thank you for your attention.
No, no, I want to group only CARDS (visa&mc), but leave the others methods unchanged

13

HI @weird_guy , understood its simple instead of “Others” you just put the Field Name Payment_Method

FYI

image
image912×250 10.8 KB

Here on the else part you can put the primary Field Name instead of ‘Others’ or ‘0’

Thanks & Regards
Biswajit Dash

1 Like
14

@Biswajit_1993 But if I have more than 10 specific payment methods, So I have to provide them instead of ‘Others’? Like ‘payment method1’, ‘payment method2’ and etc

15

Hi @weired_guy

No no simple instead of ‘Others’ put your field name so that the remaining values apart from CARDS automatically display on the visuals.

Thanks & Regards
Biswajit Dash

16

Realistically you can’t simply add percentages together and get a meaningful result. It makes no sense. What if the interest rate on Visa was 90 and the interest on Mastercard 90? 180%. It’s nonsensical.
You need an average of the rates and you need to take into account the rate at the time of each transaction. Just as a basic example, let’s take the scenario presented.

Visa: 48.444
Mastercard: 45.570
Number of Cards: 2

Sum = 94.014
Average = 94.014 / 2
= 47.007

18

Hi @weird_guy,
It’s been awhile since we last heard from you, did you have any additional questions regarding your original post?

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

Thank you!

19

Hi @weird_guy,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!