Power BI is Not summing up correctly

[lcasey]

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

[Vvelarde]

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

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!!!

[Vvelarde]

This is a related thread to this:

https://community.powerbi.com/t5/Desktop/Urgently-Need-assistance-with-relating-Data-in-Power-BI/td-...

[lcasey]

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. 

[v-micsh-msft]

Hi lcasey,

What is your current situation?

If you still need any further assistance on this topic, then please post back and we will see what we could suggest.

In addition, if you are using SSRS 2016, then it would be possible to PIN dashboard directly from SSRS to Power BI.

Pin Reporting Services items to Power BI Dashboards

Regards

[Greg_Deckler]

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