Total sum of unique values

Question & Answer
, ,
1

Hi folks

I am trying to find a way of calculating the sum of the unique values per dimension and show it as the total value in the line chart

Here’s a draft of my data set:

image
image1008×984 70.4 KB

The goal is to calculate the total sum of unique balances per account number and print it in the line chart with the Transaction date dimension.

In other BI tools, there’s a TOTAL operator we can put and calculate value regarded all dimensions, but in QuickSight i cannot find such an option

The desired result is also shown in excel line chart.
I would appreciate any thoughts or recommendations

Thank you!

2

Hi @AndriyShepel,

Isn’t this just the sum of Start Balance grouped by Transaction Date or are there duplicates that are not being shown in your screenshot?

3

Sorry, here’s the modified version - some dates might not have the attached start balance value

image
image1016×960 65.1 KB

The goal is to calculate the distinct sum of Start Balances per Account Number and show the total in the line chart. So each date in the line chart should show the same total value.

4

Can you try this?

sum(max({Start Balance}, [{Account Number}]))
5

Thanks for sharing the formula - it works great in case when two accounts contain all the Transaction dates. If i delete a few dates for the second account, the line becomes skewed:

image
image1984×1448 173 KB

I still need to show all the dates and the total sum of the unique start balances per account number, so expected to see the flat line of 25k

7

Can you try this?

maxOver(sum({Start Balance}), [])

It will be easier if you can create your dataset such that every account has a record for each transaction date, even if it doesn’t have an amount or start balance (like in your 2nd screenshot). The above calculated field works only if there is at least 1 date that has a start balance for both accounts.

8

Hi @AndriyShepel

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

9

Hi @AndriyShepel

Since we have not heard back from you, I’ll go ahead and close this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!