Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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
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.
@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.
Thank you @mwegener I really appreciate your help.
And also wanna thank @amitchandak
You both have a gift with Power BI 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |