How to Aggregate By a Dimension which is not in the View in Tableau

You can aggregate your data on a dimension that is not visible in the view. For example, suppose you want to aggregate each day’s profit but only display the results by month.  Use this example and the procedure below to calculate the average profit and display the average profit by each month.
Step 1: Find the average profit
  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, do the following:
    1. Type a name for the field. For this example, type Average Profit.
    2. In the Formula text box, type the following calculation: WINDOW_AVG (SUM[Profit]))
    3. Click OK.
Step 2: Find the average profit per month
  1. Drag the Average Profit field to Text.
  2. In the Marks shelf, right-click the field, and select Compute Using > Pane (Down).
Step 3: Show the average profit for each month
  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, do the following:
    1. Type a name for the field. For this example, type First Filter.
    2. In the Formula text box, type the following calculation: First()=0
    3. Click OK.
  3. Drag the First Filter field to the Filters shelf, right-click the field, and select Compute Using > Pane (Down).
Step 4: Create the final view
  • Right-click the Day (Order Date) field in the Rows shelf, and remove the selection next to Show Headers.
Note: For better performance, remove the First Filter field from the Filters shelf and  modify the Average Profit field formula to the following: IF FIRST()=0 THEN WINDOW_AVG(SUM([Profit])) END