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.
Hi all,
Hopefully I can get a little help with a count measure.
I have two identical tables of Items and customers who bought them (sample):
Item | Customer |
Item 1 | Bob |
Item 2 | Susy |
Item 3 | Mary |
Item 4 | Joe |
Item 5 | Joe |
Item 6 | Joe |
These tables are linked by customer name so that if you have them side by side and selected Item 5 from table one it would then show Items 4, 5, and 6 from table two.
I'm looking to create a measure that will count the items on table two that are returned excluding the identical item from table 1. So something like this:
Item | Customer | Item | Customer | Measure 1 | |
Item 1 | Bob | Item 4 | Joe | 1 | |
Item 2 | Susy | Item 5 | Joe | ||
Item 3 | Mary | Item 6 | Joe | 1 | |
Item 4 | Joe | ||||
Item 5 | Joe | ||||
Item 6 | Joe |
Hopefully there is a way to do this.
Solved! Go to Solution.
Hi @STS_Joshua
Try the following measures
Measure = CALCULATE(COUNT(Table2[Item]),EXCEPT(Table2,Table1)) Measure 2 = CALCULATE(COUNT(Table2[Item]),ALL(Table2)) Measure 3 = [Measure]/[Measure 2]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Like this:
Hi @STS_Joshua
Is this problem sloved?
If not, please let me know.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @STS_Joshua
Try the following measures
Measure = CALCULATE(COUNT(Table2[Item]),EXCEPT(Table2,Table1)) Measure 2 = CALCULATE(COUNT(Table2[Item]),ALL(Table2)) Measure 3 = [Measure]/[Measure 2]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Well I figured that out through some trial and error.
Measure1 = CALCULATE(COUNT('Table2'[Item]), EXCEPT('Table2', 'Table1'))
Now I'm having an issue with determining that item's % of that value. In the above example I'd like another measure to show Item 4 and 5 as 50% of the total count of filtered items.
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 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |