Pivot with both count distinct AND calculated fields

[maffewBHA]

Hey guys first post, good morning (well here anyway) to you all.

I have looked for a solution to this and am hitting a bit of a blank, I am sure it must be answered somewhere

Excel 2016. I want to add distinct count to a pivot table. in its simplest form, its sales rep, order counts, and item counts

So

Desired Table
Rep Orders Items
ABD 1 2
EFG 3 6
HIJ 2 2

Data
Rep OrderNo Item No
ABD 1 10
ABD 1 11
EFG 2 12
EFG 2 13
EFG 2 14
EFG 3 15
EFG 4 16
EFG 4 17
HIJ 5 18
HIJ 6 19


If when I create the pivot I select the add to data model tick, then I get the distinct count option in the field value settings , which gives me what I want (ie unique count of orders) as I am sure many of you are aware
Though when doing this I lose the option to add calculated field, it become greyed out
If I dont add it to the data model I get the calculated field option, but no distinct count as an option for the field value. I need both

So I see this is something to do with the connection/source data/OLAP blah.
Right now its a view from sql server (which is currently just copied and pasted in to the "data" worksheet).
Can I have both distinct count and calculated fields? Is there a workaround?

I need to add some calculated fields for a percentage later in the table. I do have access and rights to update the sql view, though Excel is more happily supported in my environment than tsql. Can anyone advise - also being new here I hope this meets any posting requirements and I have explained ok. Off to work now to tackle this so a heads up would be fantastic This one simple thing is holding back the delivery, errm today I can upload/send an example it be needed (if the forum supports this)

Thanks a million

 

[RoryA]

If the data is sorted on order number, it would just be something like:

=IF(I2=I1,0,1)

copied down.

 

[maffewBHA]

Thanks again for the workaround on this. It did the job, I have managed to get a "Uniqueorderidentifier" flag in the SQL view to sum instead

Quick pivot question, not sure it warrants a new thread

Im having a a hell of a game with filters not retaining their formats, orders...

My current issue is that I have a Week Commencing field. its coming up with EVERY day of the year in the format 01-Jan. And a final field value >27 August

It should only have values in SINCE the 27 August 2018, as per the data, and then just one per week!? I had it working briefly by putting it in the rows or columns where it works as expected, then moving it back to filters , but its gone back to how it was now in the filter area. Grrrrrrrrrrrrrrrrr

 

[RoryA]

Did you group the date field?

 

[maffewBHA]

Did you group the date field?

Hmm, it may have grouped itself then I ungrouped it. I cant remember, and others have used it