Forum Discussion
Solved
Counting distinct (unique) values
I have been trying to count the values in a column. I have tried the belwo Count Kit Bags = DISTINCTCOUNT(PPECheckinOut[ScanKitBagTag]) the result should be 16 but it reuturns 17 (counting th...
And this
Measure 2 = SUMX ( ALL ( PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ), CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) ) )
Count Kit Bags =
CALCULATE (
DISTINCTCOUNT ( PPECheckinOut[ScanKitbagTag] ),
FILTER (
ALL ( PPECheckinOut[ScanKitbagTag] ),
PPECheckinOut[ScanKitbagTag] <> BLANK ()
)
)
HI Zubair
that did the trick thanks.
it has however shown one more issue. in one of the column i need to count there are quantities.
ScanKitBag Crabs
12 2
13 4
14 2
15 2
16 4
16 4
In this instance i want to sum [Crabs] but exclude the duplicate bag numbers (16 counted twice.) So the out come of this instance would be 14 and not 18.
Try this
Measure = SUMX ( SUMMARIZE ( PPECheckInOut, PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ), CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) ) )And this
Measure 2 = SUMX ( ALL ( PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ), CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) ) )Thanks for this. One little thing is how do i make this a whole number without decimal points?