寻求正确DAX公式：计算列最小值及行值与最小值的百分比差值

Hey there! Let's break down how to build those third and fourth columns you need using DAX. I'll assume your table is named YourTable and the second column (the one you're basing calculations on) is called ValueColumn — just swap these out for your actual table/column names.

1. Get the Global Minimum (Third Column) #

First, we need to show the overall minimum of your value column on every row.

If you want a calculated column (static, updates only when your data refreshes):

Global Minimum = MIN(YourTable[ValueColumn])

If you need a measure (dynamic, adjusts automatically to any filters or slicers you apply):

Global Minimum Measure = CALCULATE(MIN(YourTable[ValueColumn]), ALL(YourTable))

The ALL(YourTable) part ensures we ignore any row-level filters and grab the minimum from the entire table. If you ever need to calculate the minimum per group (e.g., per category), just replace ALL(YourTable) with ALLSELECTED(YourTable[CategoryColumn]) to keep category-specific filters active.

2. Calculate Percentage Above Minimum (Fourth Column) #

Next, let's compute how much each row's value is above that minimum, expressed as a percentage.

Calculated Column Version #

% Above Minimum = DIVIDE(YourTable[ValueColumn] - YourTable[Global Minimum], YourTable[Global Minimum], 0)

Measure Version #

% Above Minimum Measure = 
VAR GlobalMin = CALCULATE(MIN(YourTable[ValueColumn]), ALL(YourTable))
RETURN
DIVIDE(SELECTEDVALUE(YourTable[ValueColumn]) - GlobalMin, GlobalMin, 0)

• Use DIVIDE instead of plain / because it safely handles cases where the minimum might be zero (the third parameter 0 is what we return if division by zero would occur).
• Once you create this, just set the column/measure's format to Percentage in Power BI/Power Pivot to get a clean percentage display.

Quick Tips #

• Make sure your ValueColumn is a numeric data type — text or blank values might cause unexpected results (though MIN will automatically ignore blanks).
• If you need to restrict the minimum calculation to a subset of data (e.g., only rows where a status is "Active"), add a filter to the CALCULATE function, like:

  CALCULATE(MIN(YourTable[ValueColumn]), ALL(YourTable), YourTable[Status] = "Active")
  

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