Forum Discussion

Sania-F's avatar
Sania-F
Resolver I
11 months ago

VALUES function with HASONEVALUE and SUMX iterator function

sales commission iterator =
SUMX(
    VALUES('Sales Territory'[Region]),
    CALCULATE(
        [Revenue]
        * IF(
            VALUES('Sales Territory'[Country]) = "United States",
            0.15,
            0.1
        )
    )
)
----------------------------------------------------------------------------------
sales commission = [Revenue] *
if(HASONEVALUE('Sales Territory'[Country]),
 IF(VALUES('Sales Territory'[Country]) = "United States", 0.15,0.1))
-----------------------------------------------------------------------------------

  • Iterator version = correct when multiple values exist, because it loops through each row and applies the right logic.

  • Simple version = works only when the filter context guarantees one country at a time. If multiple exist, it miscalculates.


  • In sales commission iterator, SUMX iterates through each region, evaluates [Revenue] * 0.1, then sums up.

  • In sales commission, you’re multiplying the total [Revenue] in context by 0.1.


Rule of thumb:

  • Use simple measure if your business rule applies a single rate per filter context.

  • Use iterator (SUMX) when you need row-by-row logic, e.g., different commission rates for different countries/products.