Need help with Distinctcount, DAX formula

[shophoney]

Hi below is my formula for counting the number of transactions. But the issue is I now need to know the NET number of transactions. So if i had 16 sales and 2 returns the total I need is 14 transactions not 18.

TRANS# DISTINCT COUNT:=DISTINCTCOUNT('SALES TABLE'[TRANS#])

Can anyone help with this. Thanks

 

[Matt Allington]

Pesumably you have a column that is negative or positive. Then

total = calculate(distinctcount(sales[trans]),sales[qty] > 0) - calculate(distinctcount(sales[trans]),sales[qty] < 0)

 

[shophoney]

Pesumably you have a column that is negative or positive. Then

total = calculate(distinctcount(sales[trans]),sales[qty] > 0) - calculate(distinctcount(sales[trans]),sales[qty] < 0)

This is what i tried:
TRANS# DISTINCT COUNT2:=CALCULATE(DISTINCTCOUNT('SALES TABLE'[TRANS#]),'SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT('SALES TABLE'[TRANS#]),'SALES TABLE'[UNITS SOLD]<0)

Here's the strange part if I add up all the transactions it's correct. But yet the total shows a higher amount?

[TABLE="width: 542"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]LOC#[/TD]
[TD]FULL NAME[/TD]
[TD]ADS[/TD]
[TD]UNITS[/TD]
[TD]RETAIL[/TD]
[TD]TRANS# DISTINCT COUNT2[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]Jennifer F[/TD]
[TD="align: right"]$173[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]$1,733[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Antonette P[/TD]
[TD="align: right"]$187[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$1,681[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Venessa B[/TD]
[TD="align: right"]$246[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$1,473[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Kaitlin E[/TD]
[TD="align: right"]$153[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$610[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Christina Q[/TD]
[TD="align: right"]$85[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$511[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Andrea M[/TD]
[TD="align: right"]$169[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$337[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Julia F[/TD]
[TD="align: right"]$230[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$230[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Leyla M[/TD]
[TD="align: right"]-$165[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-$165[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]7000 Total[/TD]
[TD][/TD]
[TD="align: right"]$173[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]$6,410[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]$173[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]$6,410[/TD]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]


Total should be 35 not 34?

 

[Matt Allington]

then you need this

[B]TRANS# DISTINCT COUNT2:=sumx(values([/B][B]'SALES TABLE'[TRANS#]),[/B][B]CALCULATE(DISTINCTCOUNT('SALES TABLE'[TRANS#]),'SALES TABLE'[UNITS SOLD]>0))-[/B][B]sumx(values([B]'SALES TABLE'[TRANS#]),[/B][/B][B]CALCULATE(DISTINCTCOUNT('SALES TABLE'[TRANS#]),'SALES TABLE'[UNITS SOLD]<0))[/B]

read here When to use SUM vs SUMX in DAX

 

[Matt Allington]

actually, take 2. This is better

TRANS# DISTINCT COUNT2 :=
CALCULATE (
    SUMX (
        VALUES ( 'SALES TABLE'[TRANS#] ),
        DISTINCTCOUNT ( 'SALES TABLE'[TRANS#] )
    ),
    'SALES TABLE'[UNITS SOLD] > 0
)
    - CALCULATE (
        SUMX (
            VALUES ( 'SALES TABLE'[TRANS#] ),
            DISTINCTCOUNT ( 'SALES TABLE'[TRANS#] )
        ),
        'SALES TABLE'[UNITS SOLD] < 0
    )


)

 

[shophoney]

Hi Matt.

I tried both. Option 2 gives the same result as before. For some reason the total is different then all the values manually added up. Never seen this before.

Option 3 gives $$ values in the thousands not sure. It is the number of transactions x the transactions???

 

[Matt Allington]

If you can post some sample data in a workbook configured, I can take a look for you. Did you read the article I posted above? this is a common 'problem' with known solutions. You just have to work out what the pivot table is doing that the grand total is not doing, and then use SUMX or similar to fix it.

 

[shophoney]

Hi Matt.

After a sleepless night. I've figured out the problem.

There was 1 sale that 2 associates sold 1 item each. That meant that it was 1 transaction for each of them, but at the same time it was only 1 transaction for the store.

So both answers are correct 34 sales took place but by associate it adds up to 35.

Not sure how to address it. And strange that the grand total in excel is not the sum of above, but more a calculation of the over all statement.

Maybe I should go back to using paper. LOL

 

[Matt Allington]

In my experience, DAX returns the correct answers. You just have to ask the right questions