DAX DIVIDE is returning the wrong value: I just want to divide 2 columns, nothing else

[jervey]

hi, i just want to divide these 2 columns, it's strange because i already tried to divide both columns by a constant and it works, i tried adding both columns and it works, i tried to subract and it works

seems like it just does not work when it is divided or multiplied, i dont get it...

right now, the DAX is like this:

• margin_bi_calculate = DIVIDE([product_gross_profit_usd], [product_price_total_usd], 0)

These are the commands that i tried:

• margin_bi_calculate = CALCULATE(SUM(calculation_table[product_gross_profit_usd]) / SUM(calculation_table[product_price_total_usd]))
• margin_bi_calculate = DIVIDE(SUM([product_gross_profit_usd]), SUM([product_price_total_usd]), 0)

is this some kind of a bug? because i tried to divide/multiply with `product_gross_profit_usd` and `product_price_total_usd` constants and it works, only when i try to divide/multiply the 2 columns where it causes problems

or is there just something wrong i have been doing?

[PC2790]

Hey @jervey ,

Can you check the data types and other properties of the columns in question?

it should be whole or decimal number.

And are you creating a new calculated column?

[jervey]

hi, thanks for the reply, i'm not sure how to check the properties of the columns, but it comes from a CSV file from a database, both are float columns

and what i did was:
1. i created a calculation table, that is just equal to the CSV data, because i don't want to touch the original data

2. i just right click the table on the right panel and click "new column"

3. and then i added the formula after googling how to do it (this is what i've settled in as of now, but it's wrong)

[PC2790]

Is it possible to share the sample file removing the sensitive data?

[jervey]

hi, thanks for the reply, im not sure if you can access this, but here is sample data

https://app.powerbi.com/links/BIPIySB3MD?ctid=648501c9-3c4a-4779-9197-71d73a5f61cb&pbi_source=linkSh...

[bcdobbs]

Hi,

If you click on the raw data table (icon on left hand side of screen) I suspect you'll see the correct values in the column you've created.

However the screen shot you've sent is of a table visual which is doing some grouping and auto aggregating your columns. (Probably set to sum).

Try setting each column to no aggregation.

[jervey]

hi, i checked the properties for both columns, they are both decimal

i already tried to remove the aggregation, and the values are correct, but the whole point why i created a calculated column was for the SUM aggregation

each row for this table is the sales, i already have the margins calculated for each of the sales point, but what i need to get is the margins for the SUM that sorted based on the customers

[bcdobbs · Accepted solution]

In that case you need a measure and not a calculated column.

Try as a measure:

DIVIDE(

SUM(TableName[product_gross_profit_usd],

SUM( TableName([product_price_total_usd]), 0)

[jervey]

hi, i just tried using measure instead of a calculated column and this works!! thank you

[jervey]

i'm sure i already tried this, but i tried it again

still no luck...