Disctinct count subtraction

[UserSam123]

Hello everyone,

I could use your help, because currently as you can see in the table my last column the inside is good, but the grand total is wrong. It's supposed to return 1.

Here are the measurements:

Nombre d'agents servis, conjoints inclus: =CALCULATE((DISTINCTCOUNT(GESTION[C_NUM_AGENT])),GESTION[BO_DOSSIER_SERVI]=TRUE)+CALCULATE(CALCULATE(DISTINCTCOUNT(GESTION[C_NUM_CONJOINT]),GESTION[C_NUM_CONJOINT]<>BLANK()),GESTION[BO_DOSSIER_SERVI]=TRUE)

Nombre d'agents servis annulés, conjoints inclus:=CALCULATE((DISTINCTCOUNT(GESTION[C_NUM_AGENT])),GESTION[BO_DOSSIER_SERVI_ANNULE]=TRUE)+CALCULATE(CALCULATE(DISTINCTCOUNT(GESTION[C_NUM_CONJOINT]),GESTION[C_NUM_CONJOINT]<>BLANK()),GESTION[BO_DOSSIER_SERVI_ANNULE]=TRUE)

Nombre d'agents servis nets, conjoints inclus old:= [Nombre d'agents servis, conjoints inclus] - [Nombre d'agents servis annulés, conjoints inclus]

[123abc]

It seems like you're encountering an issue with the grand total calculation in your table. The problem could be due to the way your measure for "Nombre d'agents servis nets, conjoints inclus old" is calculated.

From your description, it appears you're trying to subtract the count of agents who have been served from the count of agents who have been served but subsequently canceled.

Here's a suggestion to modify your measure:

Nombre d'agents servis nets, conjoints inclus old :=
CALCULATE(
DISTINCTCOUNT(GESTION[C_NUM_AGENT]),
GESTION[BO_DOSSIER_SERVI] = TRUE
)
+ CALCULATE(
DISTINCTCOUNT(GESTION[C_NUM_CONJOINT]),
GESTION[C_NUM_CONJOINT] <> BLANK(),
GESTION[BO_DOSSIER_SERVI] = TRUE
)
- (
CALCULATE(
DISTINCTCOUNT(GESTION[C_NUM_AGENT]),
GESTION[BO_DOSSIER_SERVI_ANNULE] = TRUE
)
+ CALCULATE(
DISTINCTCOUNT(GESTION[C_NUM_CONJOINT]),
GESTION[C_NUM_CONJOINT] <> BLANK(),
GESTION[BO_DOSSIER_SERVI_ANNULE] = TRUE
)
)

This calculation ensures that you are counting distinct agents served and distinct agents served as conjoints, and then subtracting the count of agents canceled along with the count of canceled conjoints. Ensure that you're subtracting the correct counts and using the appropriate filters to get the desired result. This should help in fixing the issue with your grand total.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.