How to calculate number of transactions but excluding voided ones?

[Anonymous]

Hi Power BI experts,

I have a quick question that I hope you could kindly help me with. 

I'm working on a set up sales data which includes a list of transaction ID and Product price. Below is a sample list.

I was trying to work out the total # of transactions went through as each of the transaction means one sale we made. However, I noticed in this list, there are some transactions that were actually voided (maybe because the customers didn't want the product anymore so we issued them a refund). As you can see, Transaction ID 1001 appeared twice in the list. One product price is +50 and one product price is -50. 

If I use count rows to calculate total number of transactions, the result will not accurately reflect the actually # of transactions or sales we made. 

One method for me is to remove these transactions with + and - numbers completely from the original file. 

My question is, is there a DAX I can use to calculate the total number of successful transactions without removing the voided ones in the orginal dataset and also excluding in the calculation?

Thank you very much for your help in advance!

Cathy

[smpa01 · Accepted solution]

@Anonymous change SUMX to COUNTX

[smpa01]

@Anonymous  you can write a measure like this

Measure2 = 
SUMX (
    FILTER (
        GROUPBY (
            'transaction',
            'transaction'[Transaction ID],
            "sum", SUMX ( CURRENTGROUP (), 'transaction'[Product Price] )
        ),
        [sum] <> 0
    ),
    [sum]
)

[Anonymous]

Hello, Thank you for showing me how to do this. Although I didn't get the expected result based on the DAX you provided, I still wanna say thank you for spending the time to help me! 🙂

[Anonymous]

Hi,

Thank you so much for your prompt help!

I just tried the dax you shared. Maybe my question wasn't so clear (sorry!). Could you please show me how to count the number of transaction IDs that with unvoided transactions please? In this case, the total number is 4 (ID#1002, 1003, 1004, 1005).

Thank you!

Cathy

[Daniel_Fdrvc · Accepted solution]

This is how it should look like then:

Measure2 = 
COUNTX (
    FILTER (
        GROUPBY (
            'transaction',
            'transaction'[Transaction ID],
            "count", SUMX ( CURRENTGROUP (), 'transaction'[Product Price] )
        ),
        [count] <> 0
    ),
    [count]
)

[Anonymous]

Hi Daniel,

Based on my question yesterday, may I ask a follow up question please?

I'm working on similar dataset but this time I don't have transaction ID anymore. I have 'cutsomer membership account number', and 'transaction' that is associated with each of these customer accounts. See below sample data.

This time, the list not only has transactions that are voided (can cancel each other out) but I also noticed some transactions are marked 0 (also means no sales made). 

I also wanted to see the actual number of successful transactions in the dataset (this case is 4, unhighlight ones). Do I need to modify anything in DAX you shared with me yesterday to exclude those 0 transactions or is it already excluded along with the transactions that can cancel each other out?

Thanks a lot for your kind help!

Cathy

[Anonymous]

Thank you Daniel for your help! I got the result I wanted! 🙂 Thank you for spending the time to help me!

[smpa01 · Accepted solution]

@Anonymous change SUMX to COUNTX

[Daniel_Fdrvc]

If you understand correctly, then the identifiers 1001 and 1006 should not be counted at all? In this case, the result should be 4?

[Anonymous]

Hi Daniel,

Yes you are right. I only wanted to count 1002, 1003, 1004, 1005 and exclude all 1001 and 1006 because in reality 1001 and 1006 transactions mean $0 sale.

Best regards,

Cathy