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,
Below is a simple example of the calculated column I am trying to create.
Last row showing the running count based on product and customer being the same. Order Qty is not relevant.
Product | Customer | Order Qty | Count |
A | 123 | 5 | 1 |
A | 123 | 5 | 2 |
A | 123 | 5 | 3 |
B | 456 | 5 | 1 |
C | 456 | 5 | 1 |
C | 456 | 5 | 2 |
Thanks,
Solved! Go to Solution.
Hi @rhys1987
You need to first add an index column to the table to indicate precedence. Otherwise there is no way to differentiate those rows in your count. You can do that easily in PQ. Then your calculated column:
Col =
CALCULATE (
COUNT ( Table1[Product] ),
ALLEXCEPT ( Table1, Table1[Product], Table1[Customer] ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @rhys1987
You need to first add an index column to the table to indicate precedence. Otherwise there is no way to differentiate those rows in your count. You can do that easily in PQ. Then your calculated column:
Col =
CALCULATE (
COUNT ( Table1[Product] ),
ALLEXCEPT ( Table1, Table1[Product], Table1[Customer] ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |