Power BI desktop ( direct query redshift) default sum is not giving correct results

[Anonymous]

I am trying to get sum of sales and distinct of order count and so on  . data i am getting from redshift via direct connect 

all the distinct values are aligning well but sum fileds are not reporting correctly. do i need to work on some settings to get the correct totals.  data volume is around 120 M or so. 

[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!

[v-chuncz-msft]

@Anonymous ,

Share more detailed information if possible. By the way, you may add a custom measure.

[Anonymous]

Thanks for your response.

Looking into the issue it looks like power bi is treating numeric and int data types from Redshift in same way.

i.e its truncating the decimal values and then summing it up.

sales value has already been setup as decimal number at power bi reporting end. Any suggestions how to fix this issue?

Thanks

[Anonymous · Accepted solution]

As a interim solution.

I have multiplied "Sales" by 100 ( to make the values integer  as summation are working fine with integers)

and then through measure divided it by 100.  

If any one has a better solution please share it.

Thanks

Atul