Adding values of 2 columns with similar value formats

I have 2 columns, say entity_B and amount.
entity_B can have values, for example 151B,152B.
Each of these entity_B values have corresponding amounts.

How do I add the amounts for entity_B with similar formats?

Here’s an example:

entity_B - 151B, 154B, 161B, 163B, 171B, 172B etc
Corresponding amounts are - 10,20,30,40,50,60

I need the output something like this:

150A (151B+154B) ----> 30
160A (161B+164B) -----> 70
170A (171B+172B) -----> 110

Is there a good solution? I tried using calculated field, but didn’t work correctly.
Thanks in advance!

hi @gopinathpc,

if you just want to create table of the output you indicated, I think you can create calculated field to identify 151B, 154B as 150A such as the below… and then you can aggregate the amount by the calculated filed?

concat(left(entity_B, 2), “0A”)

kind regards,
Wakana

1 Like

Hi, @gopinathpc, Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

1 Like