Count Transactions Where Items are Only From One Category

[saralyndsay]

I'm trying to figure out the best way to approach this request. 

We have sales transaction data at the sku level, with categories attached. We want to know how many transactions ONLY have items from one specific category on it, and then how many transactions have that category on it as well as others. 

Example Data:

Transaction Number	Category	Sku
123	Dog Food	1
123	Cat Food	7
124	Dog Food	1
124	Dog Food	2
125	Dog Food	1
126	Cat Food	7
126	Bird Food	8
126	Dog Food	1

So if we were looking for how many transactions were ONLY for dog food, the measure would return a count of 2 (transactions 124 and 125). Transactions that contained dog food would return a count of 3 (transactions 124, 125 and 126)

Any help would be appreciated!!

[v-xulin-mstf · Accepted solution]

Hi @saralyndsay,

You can create calculated table as:

Dog food only = 
var tab =
SUMMARIZE(
    'Table',
    'Table'[Category],
    'Table'[Transaction Number]
)
return
FILTER(
    tab,
    COUNTX(FILTER(tab,[Transaction Number] = EARLIER('Table'[Transaction Number])),[Transaction Number]) = 1 && 'Table'[Category]="Dog Food"
)

Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @saralyndsay ,

You can try measure as:

Dog food only = 
var _TN=SELECTEDVALUE('Table'[Transaction Number])
var _tab=
SUMMARIZE('Table','Table'[Transaction Number],'Table'[Category],"tab",'Table'[Transaction Number]=_TN)
return 
IF(
    COUNTROWS(_tab)=1,
    1,
    0
)

Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[saralyndsay]

@v-xulin-mstf Just trying to understand how that measure is looking at the Dog Food category. If I now wanted to look at Cat Food, how would the measure change? I don't see where the Dog Food category is indicated. 

[aj1973]

Hi @saralyndsay 

Is this good

[saralyndsay]

@aj1973 That works for counting the transactions that have dogfood anywhere, but not those that have ONLY dogfood. There are two different calcuatlions I need. 

[aj1973]

What ONLY dog food represent? where is that Category?

[saralyndsay]

It is the dogfood category, but we want to know how many transactions only have that one category on it. Ie the customer did not purchase any other categories. In my example, there are only two transactions that have dog food as the only category purchased. So the measure would return 2