Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

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]))
 
NaN.PNG
 
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.
1 ACCEPTED SOLUTION

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

12 REPLIES 12
amitchandak
Super User
Super User

Replace all divide by DIVIDE function

Example

BIB_INDICATOR = divide(SUM(Fountain[valves_with_syrup_age_bib_ok]),SUM(Fountain[valves_with_total_bib_audited]))

 

Link : https://www.dropbox.com/s/xpmi4a7vlh3txan/NaN.pbix?dl=0

Done for all divide calc.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

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.

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

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.

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

 

 

Hi @amitchandak 


Close but sill not the value, what is the purpose of the "new" in the dax?

 

Here is the excel file in another link

 

https://www.mediafire.com/file/4vgfaayw2ze47y6/kpi_correct.xlsx/file

 

Please have a look, I only know how to express it in excel and not in dax.

 

Thanks!

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.

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!

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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

 

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

 

Thanks a mill !!

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Thank you @mwegener  I really appreciate your help.

 

And also wanna thank @amitchandak 

 

You both have a gift with Power BI 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.