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])))
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 🙂