DAX度量开发需求：按组计算满足条件的占比总和

First, let's recap the core calculation logic to make sure we're aligned:

1. Calculate the total val for each id
2. For each individual record, compute its percentage of the id's total val
3. Sum all those percentages that are greater than 10% to get the final result

Step-by-Step DAX Code #

Here’s the measure that implements this logic—no intermediate columns required:

SUM_GT_10% = 
VAR TotalValPerID = CALCULATE(SUM('Table'[val]), ALLEXCEPT('Table', 'Table'[id]))
RETURN
SUMX(
    'Table',
    VAR RowPercentage = DIVIDE('Table'[val], TotalValPerID, 0)
    RETURN
        IF(RowPercentage > 0.1, RowPercentage, 0)
)

How This Works #

• TotalValPerID: This variable calculates the sum of val for the current id using ALLEXCEPT—it keeps only the id filter context intact, so it ignores other filters but stays focused on the specific id we're evaluating.
• SUMX: We iterate over each row in the table. For each row, we calculate its percentage of the id's total, then only include that percentage in the sum if it's greater than 10% (0.1 in decimal). If not, we add 0 instead to exclude it from the final total.

Verification with Your Sample Data #

Let’s confirm this matches your expected output:

• For id=1, total val is 100. The row percentages are 5%, 30%, 50%, 15%. Summing the values over 10% gives us 30% + 50% +15% = 95%
• For id=2, total val is 200. The row percentages are 60%, 30%, 5%, 5%. Summing the values over 10% gives us 60% +30% =90%

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