ACID is Atomic, Consistent, Isolated, and Durable. CAP is Consistent, Available, and Partition Tolerant.
According to the definition, both ACID and CAP have consistency. But English is a slippery language, and often, as in this case, the context of a word is important. ACID and CAP use consistency very differently.
ACID consistency means that if a transaction completes then it has fully completed and that *all* of the rules in place for the dB are met. Unique things are unique and foreign key constraints are met.
CAP consistency, on the other hand, is about the data stored on the various distributed nodes, and what logical guarantees you can make about it. It also lets you know what the system designers expect you to be able to count on. That's a much lower bar than the consistency of ACID.
That's why most high throughput systems are only key-value stores. It's relatively easy to provide high availability to eventually consistent data in the presence of a network partition. It's also easy to let you know that you're in a potentially stale state. And if that's all your database promises then that's all it has to do.
And in many cases, that's enough. If you're building an online store then your inventory should be close, but if it's not exact that's bad, but not fatal. Consider the airline purchase process. It might be very reasonable to choose to display an approximation of what's available quickly and be able to show the same set of empty seats to 100s of people at once (CAP consistency). On the other hand, when you actually sell the seat, you need to be sure to only sell it to one person (ACID consistency).
So if you ask me which consistency you need, the answer is, it depends. It depends on your particular use case and what's important to you for a specific query/transaction.