allexcept

[Drobinson1]

I am trying to calculate total usage of the a particualr item within my table. I have a flicer filtering datas to one calendar year and another smart filter selecting a customer.

When no customer is selected my total usage is correct.  When I filter a a customer I lose a small portion of the total qty.  

When I takethe last portion of the allexcept statement out(JOB_STAGE_LINES[ACTUAL_START_DATE]) it works in the sense that is gives me total usage in the entire history.  However I want to be able to select a period and have it give me only the total usage of that period regardless of the customer I select in the smart filter.

Total Usage = IF([DailyUsage]=0,BLANK(),CALCULATE(SUM(JOB_STAGE_LINES[INPUT_QTY_ACTUAL]),ALLEXCEPT(JOB_STAGE_LINES,PRODUCT_WAREHOUSE[PART_CODE],PRODUCT_WAREHOUSE[WAREHOUSE],JOB_STAGE_LINES[PartCodeWH],JOB_STAGE_LINES[ACTUAL_START_DATE])))

[Anonymous]

I got a little bit lost in the details, but want to make sure 1 basic point is super clear:

ALL(SomeTable[SomeColumn]) removes filters on that column.  So, if your date table was filtered down to just 2016... it's now going to ignore that and show you ALL years.

ALLEXCEPT on SomeTable[SomeColumn] is equivialent to   ALL(SomeTable), VALUES(SomeTable[SomeColumn]) which is to say... it removes all filters on the table, but does NOT remove filters on the columns specified.

Total Qty := SUM(Job_Stage_Lines[Input_Qty_Actual])

Total Qty - ALL Time := CALCULATE([Total Qty], ALL(Dates))

   ^ assumes you have a date table, which is best practice.

Total Qty - All Customers := CALCULATE([Total Qty], ALL(Job_Stage_Lines[Customer]))

  ^ ignores any slicer/filter on Customer... so it will give you the total qty for ALL customers (but still obey other filters)

Total Qty - This Customer := CALCULATE([Total Qty], ALLEXCEPT(Job_Stage_Lines, Job_Stage_Lines[Customer]))

  ^ obey filters on customer, but strip off every other customer.  For THIS customer, I don't care about which warehouse, part, date, etc... just give me ALL data, but JUST this customer

I dunno if any of that was helpful, but there ya go 🙂