DAX计算报错求助：按steps_order类别计算count列求和比值时提示多列无法转换为标量值

Hey there, let's get that DAX expression working properly! The error you're seeing happens because your original usage of SUM with FILTER is incorrect—SUM doesn't accept a table (which is what FILTER returns) as its second parameter, leading to the "multiple columns can't be converted to a scalar" issue.

Correct Approach 1: Use CALCULATE to Adjust Filter Context #

This is the most straightforward and idiomatic way to calculate grouped sums in DAX. CALCULATE modifies the filter context before evaluating the sum, returning a single scalar value that DIVIDE can use:

Completion Rate = 
DIVIDE(
    CALCULATE(SUM('table'[count]), 'table'[steps_order] = 1),
    CALCULATE(SUM('table'[count]), 'table'[steps_order] = 2)
)

Correct Approach 2: Use SUMX for Iterative Summation #

If you prefer an iterative approach, SUMX will iterate over the filtered table rows and sum the count values, which also returns a valid scalar:

Completion Rate = 
DIVIDE(
    SUMX(FILTER('table', 'table'[steps_order] = 1), 'table'[count]),
    SUMX(FILTER('table', 'table'[steps_order] = 2), 'table'[count])
)

Verification with Your Dataset #

Using your sample data:

• Sum of count where steps_order = 1: 303 + 47 + 7 = 357
• Sum of count where steps_order = 2: 4 + 95 = 99
• The resulting ratio will be 357 / 99 ≈ 3.606, which both expressions will compute correctly.

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