This seems entirely plausible.
If the direct effect of $X$ on $Y$ is negative, while the mediated effect is positive, these will oppose each other and, if they are of similar magnitude, result in a weakly positive or negative total effect.
This is easy to demonstrate with an example based on your causal diagram/DAG with R (hopefully this is sufficiently self-explanatory to make sense to non R users):
set.seed(1)
N <- 100
X <- rnorm(N)
M <- X + rnorm(N)
So $X \rightarrow M$ is positive with magnitude 1.
Now let us simulate $Y$ such that $M \rightarrow Y$ is positive, while the direct effect $X \rightarrow Y$ is negative (with magnitude negative 1):
Y <- M - X + rnorm(N)
then we find the direct effect, by regressing Y
on X
and M
, since conditioning on M
will block the path $X \rightarrow M \rightarrow Y$:
summary(lm(Y ~ X + M))
## X -0.92542
So we have a strong negative direct effect as expected. But when we compute the total effect we obtain:
summary(lm(Y ~ X))
## X 0.02011
which is consistent with your scenario.