# Sum Distinct?



## bigbadbrad (Apr 19, 2012)

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 (Apr 19, 2012)

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 (Apr 19, 2012)

Bingo


----------



## elliotstan (Aug 3, 2015)

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


----------

