Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Sumx returns NaN

Hi all   I am calculating with SUMX the multiplication of two columns, the totals of these 2 columns are ok, however the total SUMX returns me a NaN.   The dax I use is    SAMPLE_WEIGHTED = SU...
Need Help
Show and Tell
  • mwegener's avatar
    mwegener
    6 years ago

    Hi o59393,

     

    try this as Measure

    SAMPLE_WEIGHTED =
SUMX (
    ADDCOLUMNS (
        fountain,
        "co2", DIVIDE ( 
            Fountain[valves_with_co2_ok], 
            Fountain[valves_analized_for_co2], 
            1 
        ),
        "bib", DIVIDE (
            Fountain[valves_with_syrup_age_bib_ok],
            Fountain[valves_with_total_bib_audited],
            1
        ),
        "flavor", DIVIDE (
            Fountain[valves_with_flavour_ok],
            Fountain[total_number_of_audited_valves],
            1
        ),
        "temperature", DIVIDE (
            Fountain[valves_with_temperature_ok],
            Fountain[total_number_of_audited_valves],
            1
        ),
        "water_syrup", DIVIDE (
            Fountain[valves_with_water_syrup_proportion_ok],
            Fountain[total_number_of_audited_valves],
            1
        )
    ),
    IF ( ISBLANK ( [bib] ), 1, [bib] ) 
        * IF ( ISBLANK ( [flavor] ), 1, [flavor] )
        * IF ( ISBLANK ( [temperature] ), 1, [temperature] )
        * IF ( ISBLANK ( [water_syrup] ), 1, [water_syrup] )
        * IF ( ISBLANK ( [co2] ), 1, [co2] ) 
        * fountain[total_number_of_audited_valves]
)

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

I did one change in the pbix file. At same location.

 

 

SAMPLE_WEIGHTED = SUMX(SUMMARIZE(fountain,fountain[Survey_ID],"_new",fountain[Integral]*SUM(Fountain[total_number_of_audited_valves])),[_new])

 

 

 

Very near to your value. Now calculations are happening at Survey_id level

o59393 

Edited : Not able to download your excel to check

 

 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    The excel you shared did not contain simple_weighted calculations

     

    Also no of rows in excel is 40192 and in pbix is 40217. That could make the diff.

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      It has a different name, my apologies. This file contains all the cells, please have a look: https://www.mediafire.com/file/4vgfaayw2ze47y6/kpi_correct.xlsx/file

       

      The SAMPLE_WEIGHTED is the equivalent to PONDERATED/SAMPLE of the excel (column U) As you see column U contains multiplication of column T and G.

       

      The cell U40220 is the sum of the column U, or in other words the sumx of column T and G.

       

      The REAL sum should be 35624,0178

       

      Please let me know if I explained myself.

       

      Thanks!

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi o59393 

         

        try this calculated column.

        SAMPLE_WEIGHTED =
VAR co2 =
    DIVIDE ( Fountain[valves_with_co2_ok], Fountain[valves_analized_for_co2], 1 )
VAR bib =
    DIVIDE (
        Fountain[valves_with_syrup_age_bib_ok],
        Fountain[valves_with_total_bib_audited],
        1
    )
VAR flavor =
    DIVIDE (
        Fountain[valves_with_flavour_ok],
        Fountain[total_number_of_audited_valves],
        1
    )
VAR temperature =
    DIVIDE (
        Fountain[valves_with_temperature_ok],
        Fountain[total_number_of_audited_valves],
        1
    )
VAR water_syrup =
    DIVIDE (
        Fountain[valves_with_water_syrup_proportion_ok],
        Fountain[total_number_of_audited_valves],
        1
    )
RETURN
    IF ( ISBLANK ( bib ), 1, bib ) * IF ( ISBLANK ( flavor ), 1, flavor )
        * IF ( ISBLANK ( temperature ), 1, temperature )
        * IF ( ISBLANK ( water_syrup ), 1, water_syrup )
        * IF ( ISBLANK ( co2 ), 1, co2 ) * fountain[total_number_of_audited_valves]

        Regards,

        Marcus

        Dortmund - Germany
        If I answered your question, please mark my post as solution, this will also help others.
        Please give Kudos for support.