Tableau技术咨询：度量除法运算及行级计算实现自定义列方案

Hey there! Let's tackle your two Tableau tech questions with clear, actionable steps—no jargon overload:

There are two main ways to do this, depending on whether you need a reusable calculation or just a one-off:

• Create a Permanent Calculated Field (Best for Reuse)
  This is the go-to method if you'll need this division across multiple views:

  1. Click "Create Calculated Field" from the top menu (or right-click in the Measures pane and select this option).
  2. In the calculation editor, write your division formula using aggregate functions—critical because Tableau works with aggregated data in most views. For example:

     SUM([Total Sales]) / SUM([Number of Orders])
     

     Skipping the aggregate functions would calculate the division per raw record, which is rarely what you want for summary views.
  3. Name your field (e.g., "Sales Per Order") and click OK. It'll appear in your Measures pane ready to drag into any view.
  4. Pro tip: Avoid division-by-zero errors with the ZN() and IFNULL() functions:

     IFNULL(SUM([Total Sales]) / ZN(SUM([Number of Orders])), 0)
     

     ZN() turns null values into 0, and IFNULL() replaces any resulting null (from division by zero) with 0.

• Quick Temporary Calculation in the View
  If you only need this division once, no need for a permanent field:

  1. Drag both measures you want to use into the view (e.g., onto Rows or the Text mark).
  2. Right-click one of the measure pills, select "Quick Table Calculation" → "Custom".
  3. Enter your aggregated division formula in the editor, then confirm. The temporary calculation will appear in your view immediately.

First, let's clarify: Yes, row-level calculations are absolutely possible even when your existing columns are aggregated measures. The key is understanding the difference between row-level (per raw record) calculations and aggregated (per group) calculations, and how to mix them if needed.

First, Let's Map Your Existing Columns #

You already have:

1. Total Score: SUM([Score]) (aggregated per student/school)
2. Score Count: COUNT([Score]) (aggregated per student/school)
3. Average Score: AVG([Score]) (or SUM([Score])/COUNT([Score]), aggregated per student/school)

Common 4th Column Ideas + How to Build Them #

Here are two typical useful 4th columns, including a row-level example:

Example 1: Row-Level Score vs. Student Average Deviation

This calculates how much each individual score differs from the student's overall average (a true row-level calculation paired with aggregated data):

1. Create a new calculated field with this formula:

   [Score] - {FIXED [Student ID], [Student Name]: AVG([Score])}
   

   The {FIXED ...} LOD expression grabs the average score for each student (aggregated), then subtracts it from the raw, row-level [Score] value.
2. Drag this field into your view. If your view is grouped by student/school, Tableau will aggregate the deviation values (e.g., sum or average) by default—if you want to see individual row-level deviations, expand the student rows to show each score record.

Example 2: Student Total Score as % of School Total (Aggregated Calculation)

If you want a summary-level column showing how much each student contributes to their school's total score:

1. Create a calculated field with:

   SUM([Score]) / {FIXED [School]: SUM([Score])}
   

2. Right-click the field in the view, select "Default Properties" → "Number Format" → choose percentage to make it readable.

Key Notes for Row-Level Calculations #

• Row-level calculations operate on individual raw data records, so they don't need aggregate functions (unless you're combining them with aggregated values via LOD expressions like in Example 1).
• When working alongside aggregated columns (like your total score or count), you can use LOD expressions (FIXED, INCLUDE, EXCLUDE) to bridge the gap between row-level and grouped data.
• If your view is showing only aggregated rows (one per student/school), row-level fields will automatically be aggregated when added—adjust the aggregation (e.g., change from SUM to MIN/MAX or keep as "Dimension") in the pill's dropdown if needed.

内容的提问来源于stack exchange，提问作者Ni_Tempe