cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NadiaLR1234
Frequent Visitor

Apply measures filters in one measure

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 ?
 
 

 

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

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:

vxiaosunmsft_0-1669686108482.png

 

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.

View solution in original post

1 REPLY 1
v-xiaosun-msft
Community Support
Community Support

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:

vxiaosunmsft_0-1669686108482.png

 

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.