Distinct Count problem

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I have an issue with a DAX formula and have spent over a week trying to resolve it.

I would like to know the number of sale transactions each day. But here’s the quandary:

• If the total number of units sold after returns is zero, ignore transaction.
• If total units sold is >0 then count it 1
• If total units sold is less <0 count it -1

Here is my current formula:

TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)

 

Sample data
<table width=”427″>
<tbody>
<tr>
<td width=”98″></td>
<td width=”164″>Column Labels</td>
<td width=”165″></td>
</tr>
<tr>
<td></td>
<td>9200</td>
<td></td>
</tr>
<tr>
<td>Row Labels</td>
<td>TRANS# DISTINCT COUNT</td>
<td>SALES TOTAL UNITS SOLD</td>
</tr>
<tr>
<td>08/14/18</td>
<td></td>
<td></td>
</tr>
<tr>
<td>172</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>173</td>
<td>-1</td>
<td>-1</td>
</tr>
<tr>
<td>174</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>175</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>176</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>177</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>178</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>179</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>180</td>
<td>1</td>
<td>3</td>
</tr>
<tr>
<td>181</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>182</td>
<td>1</td>
<td>3</td>
</tr>
<tr>
<td>183</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>184</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>185</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>205</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>206</td>
<td>0</td>
<td>3</td>
</tr>
<tr>
<td>207</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>208</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>209</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>210</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>211</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>212</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>213</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>214</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>215</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>216</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>217</td>
<td>-1</td>
<td>-3</td>
</tr>
<tr>
<td>218</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>219</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>220</td>
<td>1</td>
<td>3</td>
</tr>
<tr>
<td>221</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>222</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>223</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>224</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>225</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>226</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>227</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>228</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>229</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>08/14/18 Total</td>
<td>31</td>
<td>43</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
Correct answer should be 43 units sold and 32 transactions

The issue is with transaction 206, we had a return and a sale in the same transaction. So it met both criteria being both +1 and -1 = 0

 

Thanks

[sjhc1177Participant Started: 25Replies: 28Total: 53]

See attached image that didn’t display in the post.

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Consider using SUMMARIZE before you determine whether 1 or -1. A formula like the following should work better:

Sales Trans Count :=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'SALES TABLE',
            'SALES TABLE'[TRANS#],
            "NetUnitsSold", SUM ( 'SALES TABLE'[UNITS SOLD] )
        ),
        "SalesTranCount", IF ( [NetUnitsSold] > 0, 1, IF ( [NetUnitsSold] < 0, -1 ) )
    ),
    [SalesTranCount]
)

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Wow, thanks a lot. That totally did the trick.

 

I’ve never used “ADDCOLUMNS”, I must read up on it.

I thought “SUMX” was to be avoided? Something about being slow over a large system?

 

Steven

Cheers

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Sorry to ask another question. I’m also looking to get the same formula for 1 year ago. Here is my old formula:

 

1YR AGO TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0,dateadd(dCalendar[DATES],-364,DAY))-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0,dateadd(dCalendar[DATES],-364,DAY))

 

Steven

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Steven,

Regarding SUMX, times change and the code behind the functions improves. What was thought in years gone by (e.g., 2014) does not always apply for today. Current thinking on SUMX is that in some cases it is faster than SUM. You can read more about SUM and SUMX here.

If you have a valid calendar (dates) table in your model, then you could try

Sales Trans Count Last Year :=
CALCULATE ( [Sales Trans Count], SAMEPERIODLASTYEAR ( dCalendar[Dates] ) )

[Author]

Posts