Sum of Distinct Values

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[Swamy3105]

Hi,

this is what I was expecting.

thank you so much.

Regards.,

Swamy

[Ashish_Mathur]

You are welcome.

[Vvelarde]

@Anonymous

Hi, try with this:

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

Regards

Victor

[dedelman_clng · Accepted solution]

Try this measure:

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

Hope this helps

David