Forum Discussion

dapperscavenger's avatar
2 years ago

Sumx with filter on dynamic measure

Hi!

 

I have a table which shows me the volume in the number of shipping containers expected to be needed (forecast data).  The 'current shipping' field tells me the current strategy we are using to fill the container i.e. if we are completely filling the container (FCL = full container load) or if we will ship in Pallets. 

 

 

The 'recommended shipping' field is dynamically created using a parameter. The formula is: 

 

 

Rec Shipping = if([Fcst Cont] >= [FCL Value], "FCL", "Pallet QTY") 

 

 

The idea is that we put a limit ([FCL value] - a parameter) on the calculated no of containers we will need [FCST Cont] and if it exceeds that value then we must ship in FCL, not in pallets. The FCL value is a parameter that the user is able to adjust.

 

The calculation for the rec shipping is done on a row basis - based on a unique combination of the ship from location and the ship to country.

 

My team now wants me to be able to show a summary of the no. of containers in a table before and after the recommendations.  I can easily show the number of total containers forecasted to be needed, and the number we are currently shipping in full container loads:

 

 

but I do not know how to sum the no. of containers that would be needed if the rec shipping = "FCL"

I think I need to do a sumx of [Fcst cont] filtered where rec shipping = FCL.   [Fcst Cont] is just a switch to select how much forecast we are looking at:

 

 

 

Fcst Cont = SWITCH(TRUE(), 
VALUES(DimFcst[TimePeriod]) = "3mo Fcst", sum(FactData[3mo Cont]), 
VALUES(DimFcst[TimePeriod]) = "6mo Fcst", sum(FactData[6mo Cont]), 
VALUES(DimFcst[TimePeriod]) = "9mo Fcst", sum(FactData[9mo Cont]),
VALUES(DimFcst[TimePeriod]) = "12mo Fcst", sum(FactData[12mo Cont]), 
VALUES(DimFcst[TimePeriod]) = "15mo Fcst", sum(FactData[15mo Cont]), 
sum(FactData[18mo Cont]))

 

 

 

Any thoughts?

2 Replies