1 Reply Latest reply on Nov 12, 2018 2:41 PM by Don Wise

# Calculating Average with LOD expression

Hello Tableau Gurus, I need help with the following data. I am working on financial data for a healthcare system that has 6 hospitals. I have rows of data with duplicate account numbers in multiple rows. I would like to calculate the average length of stay for each hospital. For instance, as seen in the table below I would like to calculate average LOS for Hospital X - which is (1+2+1)/3= 1.33 and for Hospital Y- (1+3)/2=2 . What LOD expression do I run on the account number and hospital to calculate average of unique/distinct cases only.

 Hospital Account Number ALOS X 123456 1 123456 1 123456 1 678945 2 678945 2 32456 1 32456 1 32456 1 Y 54564 1 54564 1 54564 1 963963 3 963963 3 963963 3 963963 3 963963 3
• ###### 1. Re: Calculating Average with LOD expression

Hello Kunal,

I don't think you need an LOD for something that Tableau can do natively.  Please see attached 2018.3 workbook and below screenshots. I modified your data structure slightly so that it makes sense to me; hopefully, it's in line with how your data is structured.

However, if you truly do need an LOD, so that you can remove ACCOUNT NUMBER, then use this:

AVG({FIXED [Hospital],[Account Number]: SUM([LOS])/COUNT([LOS])})