Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
When using the "New quick measure" button to calculate a weighed for the following example, I get a formula that doesn seem right.
Material | Year | Length |
ST | 1976 | 400 |
ST | 1956 | 120 |
PVC | 1996 | 2000 |
PVC | 2006 | 1200 |
GIJ | 1946 | 200 |
GIJ | 1936 | 320 |
When using Age, Year and Material as input, the generated formula is:
Age weighted by Length per Material = VAR __CATEGORY_VALUES = VALUES('Test data'[Material]) RETURN DIVIDE( SUMX( KEEPFILTERS(__CATEGORY_VALUES); CALCULATE(SUM('Test data'[Age]) * SUM('Test data'[Length])) ); SUMX( KEEPFILTERS(__CATEGORY_VALUES); CALCULATE(SUM('Test data'[Length])) ) )
But the results for the materials are not correct however. But when using a SUMX() instead of SUM()*SUM() the results ARE correct:
Age weighted by Sum Length per Material = VAR __CATEGORY_VALUES = VALUES('Test data'[Material]) RETURN DIVIDE( SUMX( KEEPFILTERS(__CATEGORY_VALUES); CALCULATE(SUMX('Test data';'Test data'[Age]*'Test data'[Length]);NOT(ISBLANK('Test data'[Age]));NOT(ISBLANK('Test data'[Length]))) ); SUMX( KEEPFILTERS(__CATEGORY_VALUES); CALCULATE(SUM('Test data'[Length]);NOT(ISBLANK('Test data'[Age]));NOT(ISBLANK('Test data'[Length])) ) ))
Does this mean I 'm doing something wrong or is the autogenerated formula not correct?
Solved! Go to Solution.
Hi @etrietsch,
It seems like the normal measure calculate issue on total/summary level, maybe you can take a look at following link about measure total issue:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hi @etrietsch,
It seems like the normal measure calculate issue on total/summary level, maybe you can take a look at following link about measure total issue:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hi @etrietsch
the first formula does indeed not look correct, calculation needs to be at per row level;
in fact
( 1 X 10 ) + ( 5 X 3 ) does not equal ( 1+ 5 ) X ( 10 + 3)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo, so does this mean that the autogen formula is incorrect and that this is a bug? As I now have to manually correct the formula...
it does not look too correct to me, if you post a dataset and expected results we can make a new formula
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo In my question I already posted the correct version of the formula: this one works as expected. But I was wondering why the 'out of the box' formula is incorrect? Is it a bug or is do I use the wizard incorrectly?
Hi @etrietsch,
the first formula is incorrect because it is doing a multiplication of the sum instead of a sum of the multiplication which is what the second formula is doing
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |