all and allExcept - DAX functions

Question

What is the difference between all and allexcept functions to calculative cumulative totals for the count of orders using category and products? When is all and when allexcept used?

I want to find the avg orders per customer for the region and display count of orders with applying the filter on year for year wise detail.

Answer 1

Hi Satish,

All function is used when you want to fetch data from the entire table or a few columns without filter action.

All except is used similarly to all but would fetch data that is affected after applying filters from the entire table or few columns.

Both have the same function syntax -

syn = ALLEXCEPT/ALL(table name,column name).

Both would fetch multiple values this need to be used along with calculate() or sumx(), etc functions

As per your question, 

create a column as count(orders

Create a table with a count of orders and region.

Now create a column to find count(orders)/distinctcount(cust-id) to find the orders per customer.

Now drop the field beside region to get the average order per customer for every region.