I'm not clear on which data source (primary? secondary?) that calc is in.
Do you have a one-to-many relationship of rows between the primary and secondary sources?
You can do Window_SUM thereafter
PS: A sample workbook demonstrating the issue would really help here.
Pare it down and anonymize the data sources as demonstrated in the video linked here:
Thanks for your quick response. Primary is PDS_Leads. When grouping by the Media dimension, there is one row per media type. I need the average days for each media type to know which type performs faster than the others.
Windows_SUM and Windows_AVG creates a table calculation that results in the same number for every row in the dimension.
Workbook Please with some fake data will help you faster.
When you pull stuff from the secondary data source on a blend, the only thing you can do is bring back aggregated data.
In essence your calc is requiring Tableau to do row-level work in the secondary source. And you see that it won't work.
I'm sure you have already considered swapping primary and secondary sources. For this issue, that would take care of your problem, but often that approach opens up all sorts of other problems.
I'm grasping for straws here, but somehow you need to get a way to get all your dates from the secondary to the primary. What I'm going to suggest is a hack:
Change your dates in the secondary to integer values. Like this:
INT( STR(YEAR([Order Date])) + (if month([Order Date]) < 10 then "0" else "" END) +
STR(MONTH([Order Date])) + (if day([Order Date]) < 10 then "0" else "" END) +
STR(DAY([Order Date])) )
Then bring back the average of all those values. It will give you and "average date". Bring back the count of rows. Convert that average back to a date by reversing the above calc. (You'll need to account for rounding somehow.) Do a datediff on the average date and the lead date, and multiply that by the number of secondary rows. That would APPROXIMATE the sum of all the datediffs.
I'm with Deepak, though. It would be great to have a sample setup of what you have and play with it, and see what we could come up with.
That's exactly what I am working on now, bringing that secondary data source into the primary. My first attempt at "joining" did not work due to the many:many situation, so I pursued blending. With this new roadblock, I am revisiting joining with some custom SQL to solve the problem closer to the source with M:1. Unfortunately, the extract is now getting hung at the end of the importing data step (only 344,000 rows). When I cancel, everything just locks up and I have to "end task" in Windows and restart Tableau. Really sucking in a lot of time to get this addressed.
As to sending a sample workbook, I was resisting the work of data sanitizing and cutting out all of the other worksheets to make the file clean, but it looks like I have not choice.