topic Re: Divide with denominator as measure and numerator as related values from different table in DAX Commands and Tips

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

Anonymous — Tue, 19 May 2020 14:25:30 GMT

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

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

Anonymous — Tue, 19 May 2020 15:56:56 GMT

Is this not possible?

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

Anonymous — Tue, 19 May 2020 16:29:40 GMT

Hi Anonymous ,

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!!

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

Anonymous — Tue, 19 May 2020 16:47:55 GMT

Anonymous

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

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

Anonymous — Tue, 19 May 2020 16:58:06 GMT

Hi Anonymous ,

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!!

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

Anonymous — Tue, 19 May 2020 17:26:16 GMT

Thanks Anonymous

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