Home Forums Power Pivot Distinct Count problem

Tagged: 

This topic contains 5 replies, has 2 voices, and was last updated by  tomallan 2 years, 7 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #10402

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

    #10403

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

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

    Attachments:
    You must be logged in to view attached files.
    #10406

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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]
    )
    #10407

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

    #10408

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

    #10409

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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] ) )
    
Viewing 6 posts - 1 through 6 (of 6 total)

The forum ‘Power Pivot’ is closed to new topics and replies.