Create Calculated Column by dividing columns from two different TABLES

[Anonymous]

I want to create a calculated column by dividing columns from 2 tables linked with each other. It is giving me wrong answer. How to do this. Thanks in advance!

[v-eqin-msft]

Hi @Anonymous ,

From this:

Only measure is giving the correct value and not the calculated column.

I have put the slicer of Start Time on the report based on which it will calculate max speed.

It seems that the max speed is dynamiclly based on the selected date period in slicer, right?

If so, calculated column is not available since it is static and could not be changed. You have to use measure instead.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@TheoC Thanks for your patience. I am sharing the details.

Speed1 = SUM(Table2[Volume])/sum(Table1[Time])

Time = (DATEDIFF('Table1'[start time], 'Table1'[end time],SECOND))

These are two tables linked with each other on ID.If I divide using measure, it gives correct value but if I create a calculated column, it gives wrong value.

I don't know how to share the PBIX file with you.

[TheoC]

@Anonymous if there's a relationship using the IDs in both tables, then create following calculated columns:

colRelated = RELATED ( [Volume] ) // This brings the Volume column into the other table

colDivision = DIVIDE ( [Volume] , [Time] , 0 ) // This divides Volume by Time.

Hope this helps.

Theo

[Anonymous]

@TheoC Thanks for answering. It works but gives wrong values.

Other info- I have two tables related to each other on ID.

Table 1 has columns-Start time, End Time, ID

Table 2 has columns- ID, Volume

Speed = divide volume by Time difference btw start and end time. And then need to further divide by max speed.

Only measure is giving the correct value and not the calculated column.

I have put the slicer of Start Time on the report based on which it will calculate max speed.

[TheoC]

@Anonymous What measure are you using? 

[Anonymous]

@TheoC  

Measure 2 = sum('table2'[volume])/Sum('table1'[Time])

Calculated column --> Time = (DATEDIFF('table1'[start time], 'table1'[end_time],SECOND))

[TheoC]

@Anonymous can you provide me with the outputs that you are seeing or provide example data please. Unfortunately, it's very challenging try to understand why a basic measure works as required in this instance but there are complications with the column.

[TheoC]

Hi @Anonymous 

Can you try this:

Column = 

VAR _1 = SUM ( Table1[Amount] )
VAR _2 = SUM ( Table2[Amount] )
VAR _3 = _1 / _2

RETURN 

IF ( _3 = 0 , 0 , _3 )

Hope this helps.

Theo