cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffgarlisch Regular Visitor
Regular Visitor

Count Values Based on On Category A, On Category B, or Both Categories,

I have a data set that looks like so 

dc.JPG

 

 

 

Merchant ID column contains repeated values for a specific merchant

Transaction ID contains unique values for each transaction

Credit Debit Column Flags whether the transaction is a Credit or a Debit

 

 

I'm wondering if there is DAX i could use to calculate the Counts pictured above. 

 

Count of Merchant doing ONLY Debit transactions

Count of Merchant doing ONLY Credit transactions

Count of Merchant doing BOTH Debit & Credit transactions

Count of All Merchants

 

 

This is what I have so far, its a hard value to verify manually querying the database, and I have a feeling the formulas are incorrect!

 

Counts Total Merchants:

TotalMerchants = DISTINCTCOUNT(DST_TGO[MID])

Counts Total Debit Merchants:

TotalDebitMerchants = CALCULATE(DISTINCTCOUNT(DST_TGO[MID]),CreditDebitCode[IsDebit] = 1)

Counts Total Credit Merchants

TotalCreditMerchants = 
CALCULATE(DISTINCTCOUNT(DST_TGO[MID]),CreditDebitCode[IsDebit] = 0)

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count Values Based on On Category A, On Category B, or Both Categories,

Hi@ jeffgarlisch

You need to improve your measure and do them more deeply

Count Combo = [TotalCreditMerchants]+[TotalDebitMerchants]-[TotalMerchants] 
Count Credit = [TotalMerchants]- [TotalDebitMerchants]
Count Debit = [TotalMerchants]- [TotalCreditMerchants]

Result:

4.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Count Values Based on On Category A, On Category B, or Both Categories,

Hi@ jeffgarlisch

You need to improve your measure and do them more deeply

Count Combo = [TotalCreditMerchants]+[TotalDebitMerchants]-[TotalMerchants] 
Count Credit = [TotalMerchants]- [TotalDebitMerchants]
Count Debit = [TotalMerchants]- [TotalCreditMerchants]

Result:

4.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jeffgarlisch Regular Visitor
Regular Visitor

Re: Count Values Based on On Category A, On Category B, or Both Categories,

Thanks so much! I dont know why I didnt thinkof that, I must've been too close to the problem!