DAX: subtraction work but not division

[floralsnow]

I'm having difficulty understanding how you calculate when comparing two different tables.

I created a simple mockup which exemplifies what I'm dealing with.

I have two tables which are connected to a calendar table.

I have a measure where I'm trying to divide two values where each of them exist in their own table.

I created these to make it easier to refer to the different values.

Cost = SUM('R-Cost'[Amount Eur])

Revenue = SUM('R-Revenue'[External Revenues EUR])

I then try to calculate the margin with this measure

Margin = DIVIDE('Calculations'[Cost],'Calculations'[Revenue])

but this results in zeroes! I was thinking that maybe my relationships are wrong, but, I am able to subtract.

This measure works fine for example:

Diff =

VAR _Cost = 'Calculations'[Cost]

VAR _Revenue = 'Calculations'[Revenue]

VAR _Diff = (_Revenue - _Cost)

RETURN _Diff

So how come subtraction works but not division?

 I have googled like crazy but I just don't understand what I'm missing.

[tamerj1]

@floralsnow 
I believe I know what the problem is. You are using columns as values in your schedule which is a common mistake. When added as values, columns shall be only added as measures. In your example you can use the measuures:
[Cost] & [Reveniew] instead of the the columns

[floralsnow]

I switched between using the columns directly in the visualisation and using measures, but I could see no difference.

Your post intrigued me and I tried to find more information about this. But the only real difference I could find, both in this community and elsewhere on the web, was the difference between using a calculated column and a measure, where it was stated that a measure is preferable unless you need to filter on the data.

However, in this case I didn't use a calculated column, only one of the columns which are in the table from the beginning.

How come it can be negative to use such a column in your visualisation?

[Aditya_Meshram · Accepted solution]

Hi @floralsnow 
It may be due to the Y-Axis Scale. Since you have scale of Y-Axis set at the hundred million level, the result of the division which is a fractional value between 0 and 1(for example, at the starting point it is 54M/221M which is about 0.24), is too small to be plotted on this line chart. You can check the actual values by setting the display units of the data label to none.

[floralsnow]

ok, I changed Data Label's display units to "none" and got this

I could then change it to percentage, and get the correct value. So I think it's correct now!

[Aditya_Meshram]

That's great!🙂

[tamerj1]

@floralsnow 
The 2nd formula looks to me like a calculated column not a measure. Measres have no row context and you cannore directly reference the columns.
If you create the first formula as a measure not a calculated column it should work. At least it should give some values. Of course I'm assuming the date in the visual is from the date tabl?

[floralsnow]

Do you mean "Margin = DIVIDE('Calculations'[Cost],'Calculations'[Revenue])"?
I created it by doing "New Measure"

It's still a calculated column?

[tamerj1]

Ok this is called a measure. When refering to measures inside a formula the standard is to refer directly to the measure name with referencing the table name so we anyone reads the code can distinguish beteen colums and measure. For exsmple

Table[Column]
[Measure]