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.

o59393's avatar
o59393
Post Prodigy
6 years ago

hi amitchandak 

 

thanks, the value appeared, however is not the correct result 😕 . There must be something wrong in my formula.

 

The SAMPLE_WEIGHTED should be 35596.0178

 

Which basically is the sum of all the rows that multiply integral * Total N° valves audited

 

I attach the excel that has the calculation in cell U40195

 

https://1drv.ms/x/s!ApgeWwGTKtFdhiw45SFR_qYihoPw?e=oNuUS4

 

Thank you once again.

amitchandak's avatar
amitchandak
Super User
6 years ago

Ok, If your line-level calculations are correct then it means you need force your values to get calculated at line level and then agg.

 

Now look at my pbix, not your example

How way 3 and way 4 date diff is calculated. A context/level/group by has been taken and that. Level 4 is what making sure the same goes till GT level. In your case what is that level?

We can try same

https://www.dropbox.com/s/y47ah38sr157l7t/Oder_delivery.pbix?dl=0

  • o59393's avatar
    o59393
    Post Prodigy
    6 years ago

    hi amitchandak 

     

    I saw the dax of your pbix, but no idea which one fits best. My case I think has to be solved with sumx, regardless of any date.

     

    I dont know how to express it in dax, so I did the excel that shows the calculations.

     

    Thanks a mill.

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