Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measurement SUM

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 
 
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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]) 

       

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        PC2790
        Community Champion
        4 years ago

        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]))

         
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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? 😞 

       

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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