sum not working with certain data from JIRA

[Ben__]

Good day fellows.

I am breaking my head on this conundrum - helpfully there are one of you cleaver chaps that can point me in the direction of a solution.

Here's the scenario:

I extract data from a JIRA Server via the JIRA search API.

One of the fields with which I need to do calculations is the Story Point field.

The Story Points field is in text format when API returns the value, therefore I renamed it to StoryPoints(text) and that is the only  manipulation done on it in the data query.

This image shows the data as it is populated in the query:

The StoryPoints(text) data is displayed in a grid: the data is correct although it is still in text format as depicted here:

To perform calculations with Story Points data, I create new column:

Story Points = VALUE([StoryPoints(text)])

Here is result when I add the new Story Points  column to the grid:

Now this is where my headache appear that drives me up the walls.

As can be seen in the previous screen clip, the column is Not summarize 

But look what happens to the values in the Story Points column when I set the field to SUM! 

as depicted in the screen clip below...

The values in the Story Point column change and the total is WAY out.

I sincerely hope that some-one else had battled with a similar issue and can assist me to resolve it.

I tried the following alternatives as well, but keep getting the same result:

1. Transpose the field in the query to a Number field
2. Change they field type in the query to Whole Number
3. Replace null values in field/columnn with zeros.
4. Created a Measure instad of a column

Any help, directions, suggestions or preferably the solution will be greatly appreciated.

Thank you in advance, much appreciated!

Regards

Ben

[Greg_Deckler]

Can you supply sample/example data that can be copied and pasted?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ben__]

---

@Greg_Decklerwrote:

Can you supply sample/example data that can be copied and pasted?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

---

Thank you very for your interest and trying to help @Greg_Deckler, it is much appreciated.

I am a bit reluctant to publish and share .pbix file in a public forum becuse it contains connection details to the a company's internal data source. But, if you are willing to assist and look at the issue in detail, please send me an e-mail to ben.vandenberg.za@gmail.com and I will send you the .pbix file via e-mail. Hope you are still willing to assit.

I have also tried the following;

Exported the data as displayed in the grid as depicted on the last image into a .csv file.  Then use that as a data source into a new .pbix - but when I access that .csv data source the problem doesn't occur (I overwite the data query not to automatically detect data types. I suspect that the export from PowerBI grid into the .csv file replace the null values with a blank (empty) value.  Which cannot be detected as a null in the .csv file. Therefore the issue doesn't appear when I use that approach. 

I suspect that the null values in the StoryPoints data from JIRA has something to do with the issue, but I cant seem to figure out how to fix it while accessing the data from JIRA.

I tried to create a new Story Points (decimal type) field with the following formula:

Story Points = IF(ISERROR(VALUE(GetIssues[StoryPoints(text)])),0,VALUE(GetIssues[StoryPoints(text)]))

Then I get this result with Don't summarize option:

Without SUM option

... and this result as soon as I use the SUM option:

With SUM option

Hope you have some ideas, what I can do to prevent the value inconsistencies when using SUM.