cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Memorable Member
Memorable Member

Re: allexcept

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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors