Problem combination DISTINCT function with ALL function

davecrt

New Member
Joined
Jun 3, 2013
Messages
7
Hi guys. First of all thanks for your attention. Easy question
I'm using this syntax in excel power pivot table
=DISTINCT(ALL(Table2[sedi]) to get as result all the unique values that the field "sedi" displays, despite filters applied in the corresponding pivot table
But i got as result "The DISTINCT function expects a column reference expression for argument '1', but a table expression was used."
Any tips???
thnx
Dave
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
The DISTINCT function expects a reference to a column, and does not accept column expressions like ALL(Table2[sedi]) or VALUES(Table2[sedi]), ...

However, even if the DISTINCT function accepted an ALL( ...) expression as an argument, your expression would not work as whole, because it would return a set of values, where a scalar result is expected.

Can you tell more about what you want to achieve?
 
Upvote 0
First of all thanks Laurent for your interest

I will explain my situation. I have a pivot table with product line as column fields and customers as rows. I want display the sum of the number of "sedi"(whic is the numbers of divisions a customer has) in the values area, without differences in terms of product lines.

My row sales data set present for each customer a different number of sedi in different records(cause a particular situation in the dataset), therefore it can happen that the CustomerX has 3 sedi in one record(with hardware as linked product line) and 2 in another record(with software as linked product line). for instance

customer/number of sedi/productline
customer1/3/hardware
customer2/2/software

In the pivot table which display product line as column fields and customers as row fields i would that the total sum of unique sedi for each customer would be displayed as the sum of all the unique values showed by the "sedi" variable(therefore in this case 3+2=5) despite the product line subdivision on the columns. I mean, considering the precedent example

Pivot tabel(value field area= populated by sedi number)

column field Hardware/Software

Row field
Customer1 5 5
Customer2
ecc...

Is it possible?
Regards
 
Upvote 0
And Laurent sorry, the initial(wrong) formula in post1 was =sumx(distinct(all(table2[sedi]));table2[sedi])
 
Upvote 0
If I understood correctly, the following configuration is possible:

customer/number of sedi/productline
customer1/3/hardware
customer1/2/software
customer1/2/ hardware

... and the expected result for this customer would be 5.

Is it correct?Let me just note that the ALL function when applied to columns returns distinct values. However, in that case, you would loose any filter information - including those related to customers.

What you could do is use the SUMX function in conjunction with a SUMMARIZE expression.

Something like
SUMX( SUMMARIZE( Table2, [Customer], [Sedi] ) , ... )
 
Upvote 0
Correct Lauren. But applying all() to the column field( in this case product line) should not just apply to the column, maintaining the relationship with the row field (in this case the customer field)?

QUOTE=Laurent C;3488213]If I understood correctly, the following


configuration is possible:

customer/number of sedi/productline
customer1/3/hardware
customer1/2/software
customer1/2/ hardware

... and the expected result for this customer would be 5.

Is it correct?Let me just note that the ALL function when applied to columns returns distinct values. However, in that case, you would loose any filter information - including those related to customers.

What you could do is use the SUMX function in conjunction with a SUMMARIZE expression.

Something like
SUMX( SUMMARIZE( Table2, [Customer], [Sedi] ) , ... )[/QUOTE]
 
Upvote 0
CALCULATE(
SUMX(VALUES(Table2[sedi]);CALCULATE(DISTINCT(Table2[sedi])));
ALL(Table2[Product line])
)

This formula given by a guy is working well. However i do not understand how it is possible that CALCULATE accepts as expression parameter a column expression (Distinct) where theoretically it should just accept a calculation function (SUM ecc..)
 
Upvote 0
That is because, your expression is calculated for each row in VALUES(Table2[sedi]), thus the DISTINCT expression only returns a single value every time.

You could actually rewrite the expression as
CALCULATE(
SUMX( VALUES(Table2[sedi]) ; [sedi] );
ALL(Table2[Product line])
)

Note that SUMX will only iterate over values of [sedi] in the current selection. Assume your data look like this:
Customer ; Sedi
Customer1; 1
Customer2; 1

The subtotals for Customer1+Customer2 would be 1, with the expression you just provided. With a SUMX + SUMMARIZE expression, the result would be 2.

Pick which one suits you best.
 
Upvote 0
Re: Problem combination DISTINCT function with ALL function[solved]

Thnx Lauren. Really a great advice!!! Now i have a real better understanding about this functions combination

Really appreciated!

Davide
That is because, your expression is calculated for each row in VALUES(Table2[sedi]), thus the DISTINCT expression only returns a single value every time.

You could actually rewrite the expression as


Note that SUMX will only iterate over values of [sedi] in the current selection. Assume your data look like this:
Customer ; Sedi
Customer1; 1
Customer2; 1

The subtotals for Customer1+Customer2 would be 1, with the expression you just provided. With a SUMX + SUMMARIZE expression, the result would be 2.

Pick which one suits you best.
 
Upvote 0

Forum statistics

Threads
1,223,951
Messages
6,175,586
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top