Calculate based on filters

[NadiaLR1234]

Hi

I have the following calculations:

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)

I need a combined measure/column to say:

AvgRental = IF(ProdTypeDesc = "", AvgC, IF(ProdTypeDesc = "", Avg) however tried this way and it's not giving the correct averages - could you please assist in the correct expression formula and indicate if it should be a measure or column ?

[v-yinliw-msft · Accepted solution]

Hi @NadiaLR1234 ,

I create a sample table:

You can try this method:

New a measure:

AvgRental =
VAR _avg =
    CALCULATE (
        DIVIDE ( SUM ( vw_REN_PBI[TotalSales] ), SUM ( vw_REN_PBI[Trns] ) ),
        ALL ( vw_REN_PBI )
    )
VAR _AvgC =
    CALCULATE (
        DIVIDE ( SUM ( vw_REN_PBI[TotalSales] ), SUM ( vw_REN_PBI[ProdNoDays2] ) ),
        ALL ( vw_REN_PBI )
    )
RETURN
    IF (
        MAX ( 'vw_REN_PBI'[ProdTypeDesc] ) = "car",
        _AvgC,
        IF ( MAX ( 'vw_REN_PBI'[ProdTypeDesc] ) = "Transfer", _avg )
    )

 The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[NadiaLR1234]

I meant AvgRental = IF(ProdTypeDesc = "Car", AvgC, IF(ProdTypeDesc = "Transfer", Avg)