Report builder - why can't I select an option to calculate the Average?

@marije.peters That usually occurs if the field type in the Show Me section is not a number.

Also if you’re pulling in a lookup (which you might be, based on your screenshot here), check if you’re pulling in the ID of the lookup, or the number within the lookup. That also would impact your ability to use the AVG function.

Thanks a lot @matthew_lind . That’s helpful to know. I checked and I can confirm that the field type for the Show Me field is indeed not a number, but a dropdown list, so that is probably the cause of the issue. However, it is not a lookup. Is there a quick workaround for how I can still pull the reports I need and calculate the average? I will need to create different reports where we show the average ICP score for different data fields/groupings (e.g. per platform, per health score). 

Sorry for intruding @marije.peters  just saw this thread

Unfortunately, dropdowns are a (more limited) string type. The only workaround I can think of is to add a new numeric field in your object. And then in the rule that upserts your ICP score field do the following:
a) Apply a Transform to the dropdown label (it has to be a string, will not accept the dropdown itself as input) using a To Number fx
or
b) Use a case statement to cast your dropdown values to the corresponding number. 

You can also do a one-off rule to test and then populate the numeric field just once so you have it ready for your report.

 

Glad we’ve at least got a cause, @marije.peters.

@romihache is a true pro, so those solutions are both solid. Think of how often you’ll need to process those drop-downs as numbers.

If just once for this report and the drop-down list is relatively small (10 values or fewer), a Case statement would be my choice.

If you see a future where you need to do more math, or if that drop-down list is long, the new field + rule to populate may be more durable.

Thanks so much both for the really helpful suggestions @romihache and @matthew_lind . Much appreciated!