CODEWITHSUNDEEP
daxpowerbi-desktop
shirlgirl
shirlgirl

Reputation: 5

DAX SUM DISTINCT values by groups

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

Upvotes: 0

Views: 4859

Answers (1)

AntrikshSharma
AntrikshSharma

Reputation: 661

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] )
)

enter image description here

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

enter image description here

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

enter image description here

Upvotes: 2

Related Questions