get infinity and empty result on measure not sure why

[Anonymous]

I have one table called cars with columns price and service:

e.g.

service       price

Leasing     20000

Leasing     15000

Leasing     16000

Buying      2000000

Buying      3000000

I added to the table the measure as below to calculate my yield of leasing a car vs buying:

Yield % =

Var Leasing= CALCULATE([Average Price], car[service]="Leasing")*12

Var Buying = CALCULATE([Average Price], car[service]="Buying")

Return Leasing/Buying*100

But I get infinity and empty result on all not sure why.  Price column is ok and Average price is just measure off average of price and also looks ok.

[v-yingjl]

Hi @Anonymous ,

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li

[v-yingjl]

Hi @Anonymous ,

Seems like you have used wrong filter expression to cause this issue.

When filter in the expression, you need to use all() to clear all the filters in the table then create a new filter term in the expression.

The right expression should be like @Tahreem24  mentioned:

Yield % =
VAR Leasing =
    CALCULATE (
        [Average price],
        FILTER ( ALL ( Car ), 'Car'[Service] = "Leasing" )
    ) * 12
VAR Buying =
    CALCULATE ( [Average price], FILTER ( ALL ( Car ), 'Car'[Service] = "Buying" ) )
RETURN
    ( Leasing / Buying ) * 100

Best Regards,
Yingjie Li

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

[Anonymous]

Wow thanks a lot for the hard work.  It sstill shows ALL INFINITY,   not sure why !

I uploeaded the data here so you can see: data.csv

https://drive.google.com/file/d/16pi6gZdT0EIczBP159iKe5LF5WxyXvm5/view?usp=sharing

[harshnathani]

Hi @Anonymous ,

Yield % =

Var AveragePrice = AVERAGE('Table'[price])

Var Leasing= CALCULATE(AveragePrice, 'Table'[offer_type]="Rent")*12

Var Buying = CALCULATE(AveragePrice, 'Table'[offer_type]="Sale")

Return

DIVIDE (Leasing,Buying)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[az38]

@Anonymous 

try

Yield % =
100 * 
DIVIDE(
CALCULATE(AVERAGE(car[Price]), car[service]="Leasing")*12,
CALCULATE(AVERAGE(car[Price]), car[service]="Buying"),
0
)

[Anonymous]

Now I got all values as zero....  strange.  I put the columns in table to see, and average price cloumn is all kind of values and the yiled % is zero....   so not working

[vanessafvg]

are you using a whole number or a decimal, add 2 decimal places as maybe you can't see the value.

[Anonymous]

It is decmila,  0.00.... very strange

[Tahreem24]

@Anonymous ,

Try the below measure:

Yield % 1 =

Var L = CALCULATE(AVERAGE('Tbl'[Price]),FILTER(ALL('Tbl'),'Tbl'[Service]="Leasing"))*12

Var B = CALCULATE(AVERAGE('Tbl'[Price]),FILTER(ALL('Tbl'),'Tbl'[Service]="Buying"))

Return IFERROR((L/B),0)

I got 8.16%.