3 Replies Latest reply on Jun 24, 2015 6:08 AM by Jonathan Drummey

# How do I sum a measure once for each dimension value?

I have a health care dataset that has the length of stay in days on each row for each patient ID. I have that table joined to a table which lists every doctor that established a relationship to said patient ID. Which is many Docs and Doc Relationships for each Pt ID. When I sum the length of stay its inflated and I dont want to always show each dimension on my views. I can use a blend of the two tables and it works but its blending on Pt ID which is a very granular dimension so the views take quite a while to render.

Is there a way with Tableau v9 new LOD expressions that i can use to help me avoid blending or table calcs?

Here is some sample data...

 Hospital PtID Rltn Doc LOS Memorial 1 att doc1 5 Memorial 1 adm doc2 5 Memorial 2 cons doc3 4 Memorial 2 att doc4 4 Memorial 2 adm doc4 4 Memorial 2 er doc5 4 Memorial 3 er doc1 7 Memorial 3 cons doc6 7 Memorial 3 att doc4 7

So how do I get the correct average or sum of length of stay for Hospital Memorial?

Average LOS would be (5 4 7)/3=5.33

Sum of LOS should be (5 4 7)=16

• ###### 1. Re: How do I sum a measure once for each dimension value?

Hi Brent,

See the attached. The LOD calculation is {INCLUDE [Pt ID] : MIN([LOS])}. This returns the LOS for each Patient ID to each view at the level of patients, so if we aggregate over that at a coarser level (like the level of hospital) it's only aggregating for each patient and not each record.

The workbook is attached.

1 of 1 people found this helpful
• ###### 2. Re: How do I sum a measure once for each dimension value?

This is excellent thank you very much there are many dimensions that I have that are coarser than patient ID like doctor relationship so many this is a huge help thank you very much!

• ###### 3. Re: How do I sum a measure once for each dimension value?

You're welcome!