Data Refresh Not Refreshing: Amount Column

[bipowerbi]

Hi,

The data set I am using is the Salesforce Reports online service. All I'm trying to do is create a kpi with the total amount of donations we've received YTD. But, it's not totaling the sum amount of donations. When I "Get Data" the amount column says "Record" for each record, and not the dollar amount. Instead, I click into the query editor and expand the column, which gives the amount, then I update the column data type to Decimal Number and Currency. All this works, but it doesn't give the the correct total sum of donations. It gives me $3.0 mil when it should be $3.1 mil. Is there something off with the column , that when I refresh , it doesn't give me the right total? I've gone into the formatting of the card and decimals settings are correct also in the power query editor, it's set to summarize the total. It seems so simple just to get the running total, but it's not giving me the true total amount. Please help. Thanks.

[ReportMaster]

Hi @bipowerbi were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the SF objects API. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-jtian-msft]

Hi, parry2k ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello, @bipowerbi .I am glad to help you.

It looks as if you are having problems with incorrect data in the total row when using the data source to display data in Desktop, it incorrectly shows $3.0 mil when it should be $3.1 mil.
As parry2k suggests, your YTD amount calculation logic and the filtering environment it is in will combine to affect the final result.
May I ask if you are experiencing this problem when you put the amount field (calculate column) directly into vsiual and use the system's own total aggregation.
If this is the case (your YTD amount calculation column's calculation logic is relying on other columns or just for the presentation of the data without any calculation) and your corresponding total value data in visual is not correct.
I would suggest you to write a metric with the same effect using SUMX.
like this.

measure = SUMX('Table','Table'[YTD column])

Sometimes using visual's own total aggregation function can result in incorrect aggregation of data. This is due to the fact that the total row does not have an actual calculation environment, and therefore may cause unexpected errors. It is generally recommended to use the iteration function of the aggregate function to create a meausre that does the same thing, instead of the original column that is causing the problem.

I hope that the following description of the resolved issue is helpful to you:
URL:
Why Power BI totals might seem inaccurate - SQLBI
Solved: Card total/table total doesn't match with the data - Microsoft Fabric Community
 

If you could share some corresponding screenshots or pbix files that don't contain sensitive information, it helps to help you identify the problem quickly.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@bipowerbi what measure expression are you using to calculate the YTD amount?