placeholder erro with distintcount function

[yamina]

Hi 

Can you please help me to solve this issue. 

I try to calculate the distincount number of "reference secondaire " which ' somme de mt facturé = 0" 

Somme Mt facturés = CALCULATE(sum(HG[montant_facture]))

I want to find the total result for each month 

in this board but my measure is not correct 

[yamina]

Hi 

Thank you very clear , it seems to do the job but I have a little difference int he result that I have to understand

[BeaBF · Accepted solution]

@yamina in my opinon your previous result wasn't correct, because the sum of 1270 + 1065 +1028 + 853 + 794 + 905 + 635 is 6550, not 6365. If you want to share the pbix file I can help you. 

Alternative approach using calculated columns and measures:

1. Calculated Column for Sum Check:

Is_Sum_Zero =
IF( CALCULATE( SUM(HG[montant_facture]), ALLEXCEPT(HG, HG[reference_secondaire]) ) = 0, 1, 0 )

2. Measure for Distinct Count Using Calculated Column:
Distinct_Count_Reference_Zero_Sum_Alt =
CALCULATE( DISTINCTCOUNT(HG[reference_secondaire]), FILTER(HG, HG[Is_Sum_Zero] = 1) )

Please if it's correct, then accept my answer as solution.

BBF

[yamina]

Unfortunately i can 't share you the file it is not supported

However I try with your second alternative measures I get the same result so I suppose that it is correct. How can i share you the file ? 

[BeaBF]

@yamina you can upload it in Drive and then share here the link. 

But if also the second result is the same, that's the correct solution. Can you make a manual check for example on one month? 

BBF

[BeaBF]

@yamina Hi!

// Step 1: Calculate the sum of "montant_facture" for each "reference secondaire"
Sum_Mt_Factures_Per_Reference =
CALCULATE(
SUM(HG[montant_facture]),
ALLEXCEPT(HG, HG[reference_secondaire])
)

// Step 2: Create a new column to identify if the sum is zero
Is_Sum_Zero =
IF(
[Sum_Mt_Factures_Per_Reference] = 0,
1,
0
)

// Step 3: Calculate the distinct count of "reference secondaire" where sum is zero
Distinct_Count_Reference_Zero_Sum =
CALCULATE(
DISTINCTCOUNT(HG[reference_secondaire]),
FILTER(HG, [Sum_Mt_Factures_Per_Reference] = 0)
)

BBF