Forum Discussion

yamina's avatar
yamina
Helper 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[montant_facture]))

 

 

 

I want to find the total result for each month 

in this board but my measure is not correct 

 

 

  • 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

5 Replies

  • BeaBF's avatar
    BeaBF
    Super 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

  • yamina's avatar
    yamina
    Helper I
    2 years ago

    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's avatar
      BeaBF
      Super User
      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

      • yamina's avatar
        yamina
        Helper I
        2 years ago

        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 ?