Count distinct with a max of 1

[tinkerz1]

Hi,

If I have a list like this

Department      Sales Item

A                          Widgit

A                          Clock

A                          Shirt

A                         Clock

A                         Shirt

B                         Clock

B                          Shirt

I just want in a pivot table to show

Deparment          Sales Item         Count of

A                         Widget                    1

A                         Clock                      1

A                          Shirt                       1

A Sum                                                 3

B                          Clock                      1  

B                          Shirt                        1

B Sum                                                  2

That is exist not the count of lines

Thanks,

Neil                                   

[MK_QSL]

Create Pivot Table

Dimension

Department

Item

Expression

COUNT(Distinct Item)

Go To Presentation Tab

Select Item and Tick Show Partial Sum

[tinkerz1]

Thanks, I had written that but missed partial sum