3 Replies Latest reply on Dec 9, 2016 8:28 AM by Shinichiro Murakami

    Fixed LOD across multiple data sources

    Chad Klein

      Hello Tableau Community!


      So I'm trying to do some metrics on some of our company assets and I have a question regarding LOD calcs across multiple data sources.  I know that the canned LOD calcs can't be used across multiple data sources, but there must be a way to accomplish what I'm attempting and and have not been able to find it.


      In my workbook I have 3 data sources: Hours, Working Hours, and Idle Hours


      What I'd like to calculate is a percentage of true idle hours, so the basic idea would be (Idle Hours - Working Hours )/Hours


      I created a calculation for True Idle with Idle as my primary data source that is the following:


      True Idle Hours = IFNULL(SUM([idle_hours]) - SUM([Working Hours].[pto_hours]), SUM([idle_hours]))


      Then I created a calculation that is Percent True Idle that is the following:


      Percent Idle ([True Idle Hours])/SUM([Hours].[hours])


      This of course doesn't work when I try to create a line graph of avg Percent Idle over time because the level of detail is wrong.  What I would normally do is create this calculation:


      {FIXED [asset] : SUM(True Idle Hours)/SUM(Hours)}


      But I can't do that because Tableau can't do LODs across multiple data sources.  I'd love to share a sample workbook, but all of the data is confidential, so I'm not able to.  Anyone have any suggestions of a fix or some literature that might point me in the right direction?