DAX SUM DISTINCT values by groups

Question

I'm new to PBI/DAX. I have a table called Opp Recs with a few columns: Group1, NumEmp, OppRec, AcctNum. I want to sum the NumEmp column if Group1 equals "Corp Employees" but only for unique AcctNum.

I used this formula but realized NumEmp was double counted for dup AcctNums: **Corp # Emps = CALCULATE(SUM('Opp Recs'[NumEmp]), 'Opp Recs'[Group1] = "Corp Employees")**

How can I sum NumEmp correctly?

Sample Data:

Group1	NumEmp	OppNum	AcctNum
Corp Employees	450	000030689	A0000123
Corp Employees	450	000030624	A0000123
Corp Employees	150	000030118	A0000662
Small Bus Emp	5	000030637	A0000737
Small Bus Emp	37	000030738	A0000755
Corp Employees	100	000030639	A0000784
Corp Employees	100	000030616	A0000784

AntrikshSharma AntrikshSharma · Accepted Answer · 2021-05-19T05:32:07

Edit: Based on the new requirement specified in the comments, please use this version of the code:

S =
CALCULATE (
    SUMX (
        SUMMARIZE ( Corporate, Corporate[NumEmp], Corporate[AcctNum] ),
        Corporate[NumEmp]
    ),
    KEEPFILTERS ( Corporate[Group1] = "Corp Employees" ),
    ALLEXCEPT ( Corporate, Corporate[Group1] )
)

Code based on the original requirement:

If you want to get the grand total for everything for unique AcctNum and Corp Employees then use this

VAR IsUniqueAcct =
    FILTER (
        VALUES ( Corporate[AcctNum] ),
        CALCULATE (
            COUNTROWS ( Corporate ),
            ALLEXCEPT ( Corporate, Corporate[AcctNum] ),
            Corporate[Group1] = "Corp Employees"
        ) = 1
    )
VAR Result =
    CALCULATE ( SUM ( Corporate[NumEmp] ), IsUniqueAcct )
RETURN
    Result

If you want to segregate the total of NumEmp based on the AcctNum then use this:

S = 
VAR IsUniqueAcct =
    FILTER (
        VALUES ( Corporate[AcctNum] ),
        CALCULATE (
            COUNTROWS ( Corporate ),
            ALLEXCEPT ( Corporate, Corporate[AcctNum] ),
            Corporate[Group1] = "Corp Employees"
        ) = 1
    )
VAR Result =
    CALCULATE (
        SUM ( Corporate[NumEmp] ),
        KEEPFILTERS ( IsUniqueAcct ),
        ALLEXCEPT ( Corporate, Corporate[AcctNum] )
    )
RETURN
    Result

DAX SUM DISTINCT values by groups

1 Answers