Creating Pivot tables based on a condition

[sanp96]

Hello everyone,

I am working on a building a Pivot table which is based off a table that is as follows:

Store Number	Product code	Order Number	isException
1	A	123445	N
2	B	737487	Y
3	A	256373	Y
3	A	984747	Y
4	B	267363	Y
4	A	747367	Y
5	C	398827	N

I want to create a Pivot table that has only those rows that has isException = 'Y' and I want the values to be the count of Order Number for that product for that store.  So, I am trying to create something like this:

Store Number	A	B
2		1
3	2
4	1	1

What I have tried so far is created a dimension Aggr(if(isException = 'Y', Store Number,), Store Number, Order Number). This is giving me values that don't seem to match the expected. Should there be any condition on the columns or on the values part?

Any suggestions on how this can be done?

Any help is highly appreciated. 

Thanks!

[Lisa_P · Accepted solution]

What you need is:

 - 2 dimensions, Store Number, Product code

 - 1 measure: Count({<isException={Y}>}[Order Number])

Once you create it looks like this:

Then drag Product code across to the measure side and you get this:

[sanp96]

Thanks a lot! This worked after adding keyword 'distinct'