cancel
Showing results for
Did you mean:
Established Member

## Sum of Distinct Values

Hello, I am trying to calculate sum of keys for the following table of data

 Application Data Subject Table Number of Keys App1 Account A 1000 App1 Customer B 500 App1 Assets C 8000 App1 Account D 1000 App1 Account E 1000 App2 Account F 200 App2 Customer G 50 App2 Assets H 150 App2 Account I 200 App2 Account J 200 App2 Customer K 50

Filter: None

Expected Total = (1000+500+8000+200+50+150)=9900

Filter: App1

Expected Total = (1000+500+8000) = 9500

Filter: App2, Account

Expected Total = 200

Filter: Account

Expected Total = (1000+200) = 1200

I have a feeling it can be done with SUMX but not sure how to do it.

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Sum of Distinct Values

Try this measure:

```KeyCount =
CALCULATE (
SUMX (
SUMMARIZE (
Keys,
Keys[Application],
Keys[Data Subject],
"NumKeys", MAX ( Keys[Number of Keys] )
),
[NumKeys]
)
)```

Hope this helps

David

3 REPLIES 3
New Contributor

## Re: Sum of Distinct Values

Try this measure:

```KeyCount =
CALCULATE (
SUMX (
SUMMARIZE (
Keys,
Keys[Application],
Keys[Data Subject],
"NumKeys", MAX ( Keys[Number of Keys] )
),
[NumKeys]
)
)```

Hope this helps

David

Highlighted
Super User

## Re: Sum of Distinct Values

@mnayar

Hi, try with this:

```Sum_DistinctValues =
SUMX (
SUMMARIZE (
Table1;
Table1[Application],
Table1[DataSubject],
Table1[Number of Keys]
),
[Number of Keys]
)
```

Regards

Victor

Lima - Peru

Proud to be a Datanaut!

Super User

## Re: Sum of Distinct Values

Hi,

Try this measure

=SUMX(VALUES(Data[Number of Keys]),[Number of Keys])

Hope this helps.