cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mnayar Established Member
Established Member

Sum of Distinct Values

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

 

ApplicationData SubjectTable Number of Keys
App1AccountA1000
App1CustomerB500
App1AssetsC8000
App1AccountD1000
App1AccountE1000
App2AccountF200
App2CustomerG50
App2AssetsH150
App2AccountI200
App2AccountJ200
App2CustomerK50

 

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
dedelman_clng New Contributor
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
dedelman_clng New Contributor
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
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Sum of Distinct Values

Hi,

 

Try this measure

 

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

 

Hope this helps.

 

Untitled.png