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 = SUMX(Fountain,fountain[Integral]*SUM(Fountain[total_number_of_audited_valves]))
 
 
As seen in the image the totals run fine, but the sumx returns a NaN.
 
Also I noticed this doesnt happen to every month, April does as seen in the image too.
 
The integral measure is basically a multiplication of measures:
 
Integral = IF([CO2_INDICATOR]=BLANK(),[BIB_INDICATOR]*[FLAVOR_INDICATOR]*[TEMPERATURE_INDICATOR]*[WATER/SYRUP_INDICATOR],[BIB_INDICATOR]*[FLAVOR_INDICATOR]*[TEMPERATURE_INDICATOR]*[WATER/SYRUP_INDICATOR]*[CO2_INDICATOR])
 
The sumx real value should be 35624.0178
 
I attach the pbix
 
 
What could be wrong with the formula?
 
Thanks.
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.

12 Replies