Calculated measure help - need total average across total of all itemIDs

my datset has usage days per week per therapy:
image

I know how to calculate total usage days per therapy:
sumOver
(
sum({usage_days}),
[{therapy_id}]
)
image

I need a calculated measure that gives me a SINGLE VALUE - Average total usage days across all therapies.
Is that possible?

For example, if I had only 2 therapies:
therapyID 859689781587992576 total usage days (across all weeks) is13
and therapyID 859766526068514816 total usage days is 56 then the average would be (13+56)/2=34.5

Hi @Eleri I think this is a great use case for the new LAC-A calculations. They are similar to the old Level Aware Aggregations (now renamed LAC-W), but they have simpler syntax and take care of some situations around double-counting values when rolling up totals (which in your use case you just want a single number, so this is perfect). It would look like:
avg(sum(usage_days), [{Therapy ID}]))

Then add that to a KPI visual.

Hi @Jesse thanks for your response, but what you said doesn’t work.
It simply gives me an error:
image

My bad, there was an extra parentheses

avg(sum(usage_days, [{Therapy ID}]))

OK, thanks - it worked now, BUT:
what i really need is to ignore duplicate rows in my dataset and only look at usage_days for each unique therapy ID. My dataset has Device (and can be other fields) which duplicates the rows. The correct AVG value for therapy_ID below is 6,but the calculated measure you gave me gives me 12 (because of duplicates in dataset):

This one of my biggest challenges in QuickSight - how to exclude duplicates in calculated measures

Do you have any other field you could use to detect which is the latest record per week per therapy? Example a date/time file when the record was added or an incrementing id? If so then you could use a denseRank() function and combine it with the answer to from @Jesse to get the right results.

If you do not have such a column, one way is to deduplicate the data via SQL when creating the dataset.

@darcoli thanks for your response. I don’t have another field but I don’t care which duplicated row gets excluded. Can I just somehow randomly pick one row and exclude the duplicates? Would you roughly know how to combine the syntax with denseRank? Getting syntax correct seems really tricky.

I can’t exclude them in SQL because the dataset feeds various visuals and I was hoping to make use of QuickSight calculated measures rather than create a separate dataset for different visuals.

image

TIA!

The thing is I had in mind to use the rank function to keep just the first (or last) entry for each week but if you do not have any other field to indicate which is first or last field than this approach won’t work.

But I see that you are using custom SQL with Redshift there. Can you just add a row number field in that custom sql query like so (add to the list of columns you are SELECTing):

 row_number() OVER (PARTITION BY therapy_id ORDER BY one_based_week DESC) as row_num

This would simply add a new column with the row number per week for each therapy id. So then essentially to keep just one row in your analysis you would just need to add a filter on row_num=1. @Jesse 's solution should then give you the right results

Hi @darcoli
one record in “Weekly Usage” is related to many in “User Data” - that’s how the duplication happens. Not inside a single dataset component (yes, i use custom sql). So I can’t write a sql on top of that to add the row_number.
The row_number trick I have used in the past where there were duplicates within the single sql query. It worked but even that is not ideal. I’m sure QuickSight is designed to be more clever than that with calculated measures. Hopefully someone will know?!

Thanks anyway!

Hi @Eleri -

This post shows how to use rank like you would row_number to remove duplicates.