Measurement SUM

[Anonymous]

Hello all, 

I think it is an easy one. But I really can't help myself. 

At the moment I am creating a measurement which is meant to be the sum of two different fields this way: 

BP in € = average(Worksheet[Price per Item])*average(Plan[Business Plan]) - I do not know why I chose "average" in the past. But for the table it worked so far. 

But now the total at the end ist not right. It should be the sum of the whole column. The figures in the column itself is right (it is always price*Business Plan) 

Do you know how to solve this problem? 

Thank you all in advance! 

Best

Gabby 

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

According to the picture you provided, there is a spelling error in your dax in the picture, HASONEVALUE, which you entered as HASONVALUE, missing an "e".

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[VahidDM]

Hi @Anonymous 

The total issue is very popular, Please check this link then you can find this issue and how to deal with that in detail:

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hello @VahidDM 

the post really sums up my problem! Thanks for that. 

EDIT: I do not know how to use this 

AVERAGE Revenue = IF ( HASONEVALUE ( 'Table'[Store] ), AVERAGE ( 'Table'[Revenue] ), SUMX ( SUMMARIZE ( 'Table', 'Table'[Store], "AVERAGEC", AVERAGE ( 'Table'[Revenue] ) ), [AVERAGEC] ) )

in the case of my formula BP in € = average(Worksheet[Price per Item])*average(Plan[Business Plan]) 

________________________________________________________________________________________________________________

Now I have tried to enter the formular for the measurement, but the formulare wont accept the first part. Could you take a look at it? It think the part after hasonvalue seems wrong. 

"Price per item" has a sum symbol, do not know if this is the reason why i cant enter it in the formula? 😞 

[PC2790]

As your formula is to calculate the average that' why the total is taking average of the column values.

The figures in the columns are fine because the calculation is happening on row by row basis.

Instead try this:

BP in € = SUMX(Worksheet,Worksheet[Price per Item]*Plan[Business Plan]) 

[Anonymous]

Hello, 

thank you for your post! That is what I am thinking of - but why do Power BI Desktop do not accept the last part of the formula. It seems that it is not possible to include the Business Plan part in this measurement? 

[PC2790]

If there is a relation between Plan and Worksheet, try using RELATED Dax.

something like:

BP in € = SUMX(Worksheet,Worksheet[Price per Item]*RELATED(Plan[Business Plan]))

[jsaunders_zero9]

Check that you don't have Averages turned on

[Anonymous]

Thank you for your message. I do not even have the option to choose from those fields. Do you know the reason why? Is it because of measurement? BP in € = average(Worksheet[Price per Item])*average(Plan[Business Plan])