Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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!
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!
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!
@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.
Hi @saralyndsay
Is this good
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@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.
What ONLY dog food represent? where is that Category?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |