How to create a Measure that displays daily workcenter load

[ezaidi]

Hi, I am trying to create a simple report that identified overloaded workcenters by workload capacity. I have the following simplified table to illustrate what I am trying to do:

As you can see here, the driving columns for this logic are the quantity, and dates against the desired workcenter. Any workcenter on a given day has a certain capacity. I just need a measure that shows me the accumulated load (qty) for that day based on all other orders in the list. 

This should be simple but Im not getting for some reason. I have tried something like this:

WorkCenterLoad = CALCULATE(sum([qty]),filter(salesorderlines,SalesOrderLines[BuildDate] = selectedvalue(SalesOrderLines[BuildDate]) && SalesOrderLines[Workcenter] = SELECTEDVALUE(SalesOrderLines[Workcenter])))

but all this does is return the same line qty amount. It does not seem to accumulate. I also tried sumx and did not acheive my desired result. 

Please help me as I've been hitting my head on a wall for a few hours already. 

[MFelix]

Hi @ezaidi ,

Try the following code:

Workcenterload =
SUMX (
    CALCULATETABLE (
        'Table',
        REMOVEFILTERS ( 'Table'[ITEM], 'Table'[Sales Order], 'Table'[Quantity] )
    ),
    'Table'[Quantity]
)

[ezaidi]

HI MFelix, thank you for giving me something to try out. Unfortunately it did not help I beleieve its because I have too many other columns in my real table. The sumx you are doing with the calculate table has removeFilters which I would have to add every other field in my table for this to work. This is not as practical. I did however find that this works for me but I have to do more testing.

!workcenterLoad= CALCULATE(sum(salesorderlines[RemainingQty]),filter(allexcept(salesorderlines,SalesOrderLines[warehouse],SalesOrderLines[CompanyCode],SalesOrderLines[reportESR]),[assemblyWC] in values(SalesOrderLines[assemblyWC]) && [~ExpectedAssemblyDate] in values(SalesOrderLines[~ExpectedAssemblyDate])))

It seems to generate the correct values but im not sure yet...

[MFelix]

Hi @ezaidi ,

Taking into account the information you gave the calculation is working, however the way you have your model setup may need some changes, especially if you are picking up columns from several tables (dimensions) and offcourse there is a need to update the formula accordingly.

Looking at your code be aware that the same issue you refer may happen since you are adding all the columns to you allexcept formula that can be changed if your context change.

Don't forget to mark you answer has aceppted so it can help others.