topic Re: placeholder erro with distintcount function in DAX Commands and Tips

placeholder erro with distintcount function

yamina — Fri, 26 Jul 2024 08:30:35 GMT

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

Re: placeholder erro with distintcount function

BeaBF — Fri, 26 Jul 2024 09:16:20 GMT

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

Re: placeholder erro with distintcount function

yamina — Fri, 26 Jul 2024 09:33:15 GMT

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

Re: placeholder erro with distintcount function

BeaBF — Fri, 26 Jul 2024 09:46:20 GMT

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

Re: placeholder erro with distintcount function

yamina — Fri, 26 Jul 2024 10:04:28 GMT

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 ?

Re: placeholder erro with distintcount function

BeaBF — Fri, 26 Jul 2024 10:07:21 GMT

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