5 Replies Latest reply on Jan 2, 2019 6:23 AM by Norbert Maijoor

    Converting Excel dashboards to Tableau 2018.2.2

    Rodney Bass

      I have been tasked with converting Excel dashboards to now be displayed in Tableau 2018.2.2.

      My current challenge is to create a measure which represents the “Annualized Turnover” of a group of employees.

       

      What I have currently in Excel:

      I have 2 data sources, which have no values in common.

      Data Source 1: we have a field which stores the count of all rows of an excel worksheet (Total Associates)

      Data Source 2: we have a field which stores the count of all rows of an excel worksheet (Total Terminations).

       

      Using Power Pivot, we were able to create a Measure using these 2 independent values (Total Terminations/Total Associates).

      Here is the formula we use: =[Count of Termination Reason]/[Count of ASSOC ID 2]

       

      However, in Tableau, when I attempt to create the same type of Measure, I encounter the message:

      Fields cannot be used from the <secondary data source>, because there is no relationship to the primary data source.

      In the Data window, switch to the <secondary data source>, and click at least one link icon to blend these data sources.

      TableauWarning.jpg

       

      In Tableau, I have 2 Data Sources: Sheet1(AllPay) and Sheet1(Term).

      In Sheet1(AllPay), I created a Measure called: calCntAssoc using this calculation: COUNT([Assoc Id])

      In Sheet1(Term), I created a Measure called: calCntTermReas using this calculation: Count([Termination Reason])

       

      These 2 measures are to represent the same 2 values we have in the Excel sheets (which count and store the rows of each worksheet).  When I attempt to use the 2 Measures in a formula on the Marks card of a worksheet in Tableau,

      Mark1.jpg

      I encounter the Warning message I presented earlier.  Fields cannot be used from the <secondary data source>, because there is no relationship to the primary data source. In the Data window, switch to the <secondary data source>, and click at least one link icon to blend these data sources.

       

      As I previously mentioned, the data has no direct commonality, so as far as I understand, I cannot use blending or joins.  If a value exists in Data Source 2, it cannot also exist in Data Source 1.

       

      My Challenge:

      How can I create this calculated value (Annualized Turnover), which Excel allows rather simply, using the Tableau platform?

      In Excel, all we did to create the Measure was reference the Count values from both worksheets in a simple formula: =[Count of Termination Reason]/[Count of ASSOC ID 2].  Again, we used Power Pivot to derive this value.

      PowerPivotMeasure.jpg

      Then we reference the new measure (AnnualizedTurnover) in a pivot table, which we display in the dashboard.

      PivotTable.jpg

      Any help or suggestions would be greatly appreciated. If my explanation is not clear enough, I am more than open to further explaining.