1 Reply Latest reply on Oct 29, 2013 11:48 AM by Ramon Martinez

    Sum over dimensions using star schema

    Meena Singh

      With Star schema as a data source, what is the way to compute derived metrics

      - I have two tables both at the user level

      user_t_fact ( Table 1)

      user_id ,date, week_id channel, program, age, total_minutes


      user_wkly_active  (Table 2)

      user_id , week_id


      I need to calculate a derived metric in Tableau

      % Reach = count distinct(usrer_id) exposed to program or channel  from table 1 for a week / count(user_id) for that week from table 2


      The challenge I am having is retaining the total count of users from Table 2 when I pull those metric to rows/column ?

        • 1. Re: Sum over dimensions using star schema
          Ramon Martinez

          He Meena,


          According to the description of your scenario you have two data sources: Table 1 and Table 2.


          I suggest to take advantage of the merge feature in Tableau. This allows you to merge your two data sources using in your specific case user_id and week_id


          Drag dimension Program to Row shelf, this make Table 1 as the primary data source

          In Data, click on Table 2, your secondary data source and make sure it is linked with Table 1 by clicking on link symbol to the right side of user_id and week_id


          Create your calculated field  % Reach as COUNTD(user_id)/COUNTD(Table 2.user_id)* 100


          Drag the calculated field %Reach to text


          If you share a package workbook with sample data, it would be easier for us to find a solution and illustrate it for you.