Sum Distinct?

[bigbadbrad]

Just started using PowerPivots and DAX. Figured out the Count Distinct function real quick, it is great. The problem i have is i would also like to show a sum distinct of the same data set. Seems like it should be possible using SUMX or CALCULATE function filtering the table by the Distinct count i already have. Not that easy though, atleast not for me.

Data Example:
Category Keyword Demand
Cat1 KW1 100
Cat1 KW2 200
Cat1 KW3 300
Cat2 KW1 100
Cat2 KW4 400

I would like to be able to sum the data at the keyword level and show distinct sum of the Demand. Where KW1 is in multiple Categories, i would still like to be able to do a sum where the demand is only summed once (distinct).
Any ideas would be greatly appreciated. Thanks...

 

[bigbadbrad]

Just struck me how to do this:
First create a new measure that takes the Max of my Demand (or min or average, doesn't matter since my demand number is always the same by keyword)
Then use a SUMX formula: SUMX(DISTINCT([Keyword]), [MaxDemand])
In this example, it gets me the correct number i am looking for...

 

[powerpivotpro]

Bingo

 

[elliotstan]

Used the tip from bigbadbrad and worked a treat. PowerPivotPro has a great write up on it.
SUMX() - The 5-point palm, exploding fxn technique - PowerPivotPro PowerPivotPro