cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Drobinson1
Helper III
Helper III

allexcept

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

1 REPLY 1
scottsen
Memorable Member
Memorable Member

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 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!