Division of columns from separate tables Best Practice

[jpt1228]

Hello, trouble shooting "related" calculation. I want to calculate the number of pallets needed to store products based on the number of cases  that fit on a pallet. I have a DimProduct that has the number of cases of product that fit on a pallet DimProduct [Cases Per Pallet] and I have a seperate table FactForecast that has the number of cases forecasted for a particular time period. When I try to create a measure such as Pallets Needed = Divide (FactForecast, FactForecast [Forecast Cases] , DimProduct [Cases Per Pallet]) PBI will not let me calculate across the tables.

I have found a solution but I don't know if it is the correct one from a best practice. I have created a measure called: Cases per pallet = SUM(DimProduct[Cases Per Pallet]) and Forecast Cases Helper = SUM(FactForecast[Forecast Cases]) Then I use that in the formula Forecast Pallets = DIVIDE([Forecast Cases Helper], FactForecast[Cases per pallet])

I have used 'Related' in another formula for something different but for some reason I get an error when trying to do this.

Thanks

[v-chuncz-msft · Accepted solution]

@jpt1228,

You may refer to the following DAX that adds a measure.

Forecast Pallets =
CEILING (
    DIVIDE (
        SUM ( FactForecast[Forecast Cases] ),
        MAXX ( FactForecast, RELATED ( DimProduct[Cases Per Pallet] ) )
    ),
    1
)

[jpt1228]

@v-chuncz-msft

This works, thanks - It looks like I cannot just divide columns and use related - Have to use SUM(Table[Column]) as opposed to just Divide (Table[Column]). that must be why I couldn't bring in a related column.