Sumx with filter on dynamic measure

[dapperscavenger]

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?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dapperscavenger]

Never mind, I am an idiot.  It was the easiest filter formula ever.  (which I thought I had tried earlier but honestly maybe I had one too many mince pies this christmas because my brain has turned into mince)

Fcst Cont Rec =

 CALCULATE([Fcst Cont], FILTER(FactData, [Rec Shipping] = "FCL"))