calculate the sum in a column (quantity) for just unique values in another column (category)

[Anonymous]

Hello, I am trying to create a measure in DAX that calculate the sum in a column (quantity) for just unique values in another column (category). The paramenter "quantity" is always the same in the same category.

To explain better, a have this table:

        category              | quantity

              1                    |        1

              1                    |        1

              1                    |        1

              2                    |        1

              3                    |        2

              3                    |        2

And I want to have:

         category             | quantity

              1                    |        1

              2                    |        1

              3                    |        2

SUM = 1+1+2

In SQL I obtain that sum by doing this:

 with total as (

select Distinct(category), quantity FROM table.name )

Select SUM(quantity) from total

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try this:

Total = 
SUMX (
    SUMMARIZE (
        Table1,
        Table1[category],
        "Distinct", DISTINCT ( Table1[quantity] )
    ),
    [Distinct]
)

[edhans]

You could do this in one step, but I split steps out so the logic is easier to see. This will return 4 from your sample data if you drop this measure in a card:

Grand Total = 
VAR varUniqueRecords =
    DISTINCT( Data )
VAR Result =
    SUMX(
        varUniqueRecords,
        [quantity]
    )
RETURN
    Result

[lbendlin]

All you need to do is remove duplicate rows in your first table.