Forum Discussion

yamina's avatar
yamina
Icon for Helper I rankHelper I
2 years ago
Solved

placeholder erro with distintcount function

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[mon...
  • BeaBF's avatar
    BeaBF
    2 years ago

    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

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

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