Report Summarizing Not Showing Expected Result

SteveE

I created a report to show baseline variance metrics for all projects in our portfolio. In the report, I tried to summarize the project variance field, and you can see the result below. I do not get the expected results using either average or sum.

Is it because the project variance field display in the format xxd rather than xx (e.g. 15d vs 15). And if so, does the baseline variance field exist anywhere without the “d” or do I need to parse the field or find a different way?

As always, thank you in advance!!!

Genevieve P.

Hi @SteveE

Yes, this has to do with the "d" in your values - I can see that you're using Baseline and Variance, and if you were to use a grouped report on the source sheets then it can find that Average or Sum for you:

However in your Report it looks like the Variance values are being brought into another sheet, using either cell-links or formulas (hence the blue arrows).

In this case, the "baseline value" is translated into text, "-60d" instead of the value "60". This is why the Report is unable to create an Average or Sum text.

There are a few ways you can adjust this. I would personally use the VALUE(-) function to wrap around whatever formula is being used to return the data as a value instead of text. Or if it's a cell link, you could add a helper-column into your source sheet with the VALUE function to make it a plain number, then cell-link that column instead.

Let me know if this makes sense and helped!

Cheers,
Genevieve

Genevieve P.

Hi @SteveE

Yes, this has to do with the "d" in your values - I can see that you're using Baseline and Variance, and if you were to use a grouped report on the source sheets then it can find that Average or Sum for you:

However in your Report it looks like the Variance values are being brought into another sheet, using either cell-links or formulas (hence the blue arrows).

In this case, the "baseline value" is translated into text, "-60d" instead of the value "60". This is why the Report is unable to create an Average or Sum text.

There are a few ways you can adjust this. I would personally use the VALUE(-) function to wrap around whatever formula is being used to return the data as a value instead of text. Or if it's a cell link, you could add a helper-column into your source sheet with the VALUE function to make it a plain number, then cell-link that column instead.

Let me know if this makes sense and helped!

Cheers,
Genevieve

SteveE

It did help. Sorry it took so long for my reply. I ended up creating a helper column on the source sheet using the VALUE function. Thank you very much!