Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
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:
Thanks a lot! This worked after adding keyword 'distinct'