ALL not working

[AnetaK]

Hi,

I am working on one table on sales with columns:

- table[Customer]

- table[Product]

- table[Order_date]

- table[Sale]

and measure:

- Sales=SUM(table[sale])

I want to create an average sale value for all customers and then find whether a specific customer spends more or less $ than average.

I've created a formula:

AvgSales = CALCULATE ( DIVIDE [Sales], DISTINCTCOUNT ( table[Customer] ), ALL ( table[Customer] ))

It works when I show it as value in the Card. It works, when I show it in a table with:

-Product

-Sales

-Avg sales

-Deviation from avg ( [Sales] - [AvgSales] )

But when I choose a specific customer on the slicer, Avg sales equals company Sales and not the total Sales for the product.

What do I do wrong?

[v-yuta-msft · Accepted solution]

Hi AnetaK,

I have made a sample data based on your description like below:

If works well when no customer has been selected in slicer:

To achieve the total value of products when a customer has been selected, you can modify measure [Avg sales] like this:

AvgSales = CALCULATE ( DIVIDE([Sales], DISTINCTCOUNT ( 'Table'[Customer] )), ALLEXCEPT('Table', 'Table'[Customer]))

PBIX here: https://www.dropbox.com/s/0plppnuj6qcaikc/For%20AnetaK.pbix?dl=0

Regards,

Jimmy Tao