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] ) ) )
You can do 2 things. Either create a new blank query and use Advanced Editor to paste in my code. Or, click on Enter Data in the ribbon and copy and paste the data you posted including the column header.
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] ) ) )
