Decimal part removed when properties set to SUM

[rashimittal90]

Hi,

We have a column with the format as decimal.

When we add this to table and set properties as sum, the decimal part is removed from the values. (not round off, direct whole number)

PFA a screenshot where the column has been added twice. When it is not summarised, we can see the decimal part, when summarised, we see zeroes

Please let us know if someone has a resolution, how to keep the actual decimal part.

[Anonymous]

Hello,

I stumbled upon the same issue when working with Power BI and Redshift. rashimittal90's solution works as described but consumes more processing power than needed; a better unexpected solution is to use the Power Query editor in Power BI and apply a Standard Transformation to the column such as Multiplying the value by one or adding 0 to the value.

The standard query without the transformation casts the return value as decimal which in Redshift defaults to using 0 decimal values:

select sum(CAST("mov_div_amount" AS DECIMAL ) ) as "C1" from ( select "mov_div_amount" from "main"."public"."facmovementdivision" where "mov_div_date" >= TIMESTAMP '2018-12-31 00:00:01') as "ITBL"

After the suggested transformation is applied, the query instead returns a double and looks like the following:

select sum("C1") as "C1" from ( select CAST("mov_div_amount" AS DOUBLE PRECISION ) * CAST(1 as float8) as "C1" from "main"."public"."facmovementdivision" where "mov_div_date" >= TIMESTAMP '2018-12-31 00:00:01') as "ITBL"

This solution is viable when working with two decimals or less because the default precision for double in Redshift is 2. The ideal solution would be for the Power BI Redshift connector to avoid this cast when using numeric/decimal fields.

Here's an image of two visuals comparing the same value before and after the suggested transformation:

I hope this helps!

[sinanalmac]

hi @Anonymous 

Can you Send your file?

best regards
Sinan