Divide with denominator as measure and numerator as related values from different table

[Parthsh93]

Hi

I am trying to define measure that gives "cost per kilometer"

I have a fact table having [Equipment], [Loaded Distance] and [Deadhead Distance] column

a dimention table called fixed Cost having [Equipment N] and [Fix Cost] columns

I have a measure

Equipment KMS = CALCULATE(SUM('Prototype Modeling_Freight'[Loaded Distance]) + SUM('Prototype Modeling_Freight'[Deadhead Distance]))

Shown as a table visualization

Through a relationship connection between Equipment and Equipment N

MY REQUIREMENT :

A measure defined for each equipment as [Fix cost] / [Equiment KMS]

TIA

[harshnathani]

Hi @Parthsh93 ,

You can use this measure.

Measure = Divide (Calculate (Sum ('Table'[FixedCost])), [Equipment Kms])

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a  Kudos!!

[Parthsh93]

@harshnathani

Thanks for replying

I tried your formula as

FC Per KMS = Divide (Calculate (Sum ('Fixed Cost'[Fix Cost])), [Equipment Kms])

But not getting correct results

[harshnathani]

Hi @Parthsh93 ,

You can try this.

Divide (Calculate (Sum ('Fixed Cost'[Fix Cost]),ALLEXCEPT (Fixed Cost, 'Fixed Cost' [Equipment])), [Equipment Kms])

Also, convert your measure into decimals and make the values are 'Dont Summarize'

Imcase this does not work, please share some sample data.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Parthsh93]

Thanks @harshnathani 

This didnt work either

Sorry I cant share data in document as I am restricted to do that

Although, I would like to know one thing, can we use unique related values from a dimention table and use them in the expression ?

for eg:

the column [equipment] in my fact table is related to [equipment N] in dimention table.

Now what I want is to pull this fix cost from dimention table and use it in an expression, so that it goes to dimention table and give me the related value for every row iteration.

I am able to use it in a direct table visualization, but can't use it in a mathematical expression

[Parthsh93]

Is this not possible?