Divide columns from two different tables in calculated column

[Anonymous]

Hello everyone,

I am trying to divide speed with Max speed. I created a measure to calculate speed which works fine. But when I try to divide this measure by its max value, it does not accept the code and says-

"Column 'Speed1' in table 'Table2' cannot be found or may not be used in this expression."

So instead of a measure, I tried to calculate speed in a calculated column. But it is giving the wrong value.

Speed 1 is measure n speed 1 col ios calculated column.

So I wanted to do the following-

Speed1 = divide volume by time 

Final speed = (Speed1/max speed1)*100

Here is the data-

These two tables are linked with each other on ID

I used the following code -

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

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

Thanks in Advance! Looking forward to it!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below to get the Speed2 base on the measure [Speed1] :

Speed2 = 
VAR _tab =
    SUMMARIZE ( ALLSELECTED ( 'Table1' ), 'Table1'[ID], "@speed", [Speed1] )
VAR _maxspeed =
    MAXX ( _tab, [@speed] )
RETURN
    DIVIDE ( [Speed1], _maxspeed ) * 100

Best Regards

[calerof]

Hi @Anonymous ,

You can use this code:

Percentage vs Max Speed = 
VAR MaxSpeed = 
CALCULATE(
    MAXX(Table_Speed, Table_Speed[Speed]),
    ALL(Table_Speed)
)

RETURN
DIVIDE(
    Table_Speed[Speed],
    MaxSpeed
)

Hope it helps.

Regards,

Fernando

[amitchandak]

@Anonymous , A column with measure will not work properly.

Create speed 1 Col as measure