Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago

Power BI is Not summing up correctly

Hello,

 

I have found that Power BI does not Accuratley summarize columns in the Table Matrix.

 

 

First Column:    Balance USD = IF('BSA00-RMMC'[RMDTYPAL]<7,'BSA00-RMMC'[ORTRXAMT],IF(ISBLANK('BSA00-RMMC'[APPTOAMT]),-'BSA00-RMMC'[ORTRXAMT],-'BSA00-RMMC'[APPTOAMT]))

 

Second Measure: Balance XRate = IF(SUM('BSA00-RMMC'[Balance Local])=0, Sum('BSA00-RMMC'[Balance USD]),(SUM('BSA00-RMMC'[Balance Local]) *  [EXCHRATETOUSE]))

 

Third Measure:  Gain/Loss = [Balance XRate] - sum('BSA00-RMMC'[Balance USD])

 

As you will notice in the picture, within the table Power BI is performing the correct calculation, but it does not show the correct total at the bottom

 

The Correct Total is:

$1,061,951

 

 

 

 

 

 

 

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Most likely, Power BI is doing exactly what you are telling it to do. Remember that measures are evaluated within their current context. So, a measure in a table will not simply provide a sum of the rows displayed in the table, it will evaluate in the context of essentially ALL of the data in the dataset. So, you need to design your measure a little differently if you want to use it in this way. I believe I have a post on this in DAX Commands & Tips forum. Yep, here it is:

    http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

     

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    lcasey

     

    The issue is the [EXCHRATETOUSE]  when is all customer selected (Total) his value is blank.

     

    To solve this create another measure [EXCHRATETOUSE2] and Add before the last ) this ,1

     

    Use [EXCHRATETOUSE2] in your second measure

      • lcasey's avatar
        lcasey
        Icon for Post Prodigy rankPost Prodigy
        9 years ago

        Hello Vvelarde,

         

        Would you be available as a paid consultant to complete a few reports we are developing in Power BI?

         

        Would be happy to pay you for your time. 

    • lcasey's avatar
      lcasey
      Icon for Post Prodigy rankPost Prodigy
      9 years ago

      Thanks!

       

      I see that I need to get studying DAX language. I am not really understanding this, I develop many reports in SSRS and Power BI is completely different.  I can Understand some SQL language but this language is like trying to learn Chineese...:)

       

      I am going to finish all my calculations so that Users can export the dataset into Excel and perform the calculation in Excel. 

       

      I guess I will need to carefully go through any books and online help with DAX , as all the suggestions here are absolutely correct and will fix the issue, I just have no concept of the hasonefilter or hasonevalue DAX function. I cant wrap my head around it at the moment.

       

      I wonder if I would have a better career learning the Chineese Language...:)  Might be a little easier...:)

       

      Thanks For all your help!!!