Apply measures filters in one measure

[NadiaLR1234]

Hi

I have the following averages - on their own they work fine

Avg = DIVIDE(SUM(vw_REN_PBI[TotalSales]),SUM(vw_REN_PBI[Trns]),0)

AvgC = DIVIDE(SUM(vw_REN_PBI[TotalSales]),SUM(vw_REN_PBI[ProdNoDays2]),0)

However I want to combine them into one measure/column so I can use one field on both condtions 

example I have tried: AvgRental = IF(ProdTypeDesc = "Car Rental", AvgC, IF(ProdTypeDesc = "Transfers",Avg,0)

but this is not calculating the averages correctly ?

Please assist with the correct formulation expression and if the new field should be a measure or a column ?

[v-xiaosun-msft · Accepted solution]

Hi @NadiaLR1234 ,

According to your description, I suggest that you can use calculated columns to return "Avg","AvgC" and "AvgRental".

Measure carries the formula which eventually gives the output, so when we use measure1 in another measure2, it will calculate according to the formula of measure1 instead of the result of measure1.

Create three calculated columns.

average = DIVIDE(SUM('Table'[total sales]),SUM('Table'[trns]),0)

averagec = DIVIDE(SUM('Table'[total sales]),SUM('Table'[prodnodays]),0)

Column = IF('Table'[type]="car rental",[average],IF('Table'[type]="trans",[averagec],0))

Final output:

Best Regards,
Community Support Team _ xiaosun

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