Forum Discussion

o59393's avatar
o59393
Post 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
Super 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.

  • mwegener's avatar
    mwegener
    Most 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.

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    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.

  • o59393's avatar
    o59393
    Post 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!

  • o59393's avatar
    o59393
    Post Prodigy
    6 years ago

    mwegener  wow, looks good, I got 35624,0178. The exact result.

     

    Just one question, cant this calculated column be a measure? 

     

    Thanks a mill !!

  • o59393's avatar
    o59393
    Post Prodigy
    6 years ago

    Thank you mwegener  I really appreciate your help.

     

    And also wanna thank amitchandak 

     

    You both have a gift with Power BI 🙂