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]

Welcome to the forum.

Do you have Power Pivot?

 

[maffewBHA]

Welcome to the forum.

Do you have Power Pivot?

Thank you for the welcome
I do indeed have power pivot. I havent used it (other than opening it yesterday). It looks straightforward to do what I want in power pivot I think. Though Im not going to be able to used that for this particular exercise.

My users dont have power pivot I am afraid. We are all on the same version of Excel however, 2016

 

[RoryA]

I have to say, I've never tested to see if a workbook with measures created in PP would work for a "regular" 2016 user who doesn't have PP. Might be worth a test?

(There was a time I'd have put money on that working, but not these days, and trying to locate useful information about this sort of thing is, at least for me, nigh on impossible)

 

[maffewBHA]

I have to say, I've never tested to see if a workbook with measures created in PP would work for a "regular" 2016 user who doesn't have PP. Might be worth a test?

(There was a time I'd have put money on that working, but not these days, and trying to locate useful information about this sort of thing is, at least for me, nigh on impossible)

I know! thas why I came here

I would be delighted to test it. Just not on this exercise

 

[RoryA]

You don't need to do the test on this actual project. Just create a simple table, load it into PP, add a measure and see if it works on a user machine.

Or you could ask a question in the Power BI forum here, where people know more about this stuff than I do!

 

[maffewBHA]

Is there perhaps a formula I can add as a calculated field to get distinct values? I dont need it in a data model other than to get a count distinct which takes out my other options.

I have a bit of a time contraint so I might have to hack something together in SQL. Doesnt see unreasonable to achieve this in Excel does it?

 

[maffewBHA]

Is there perhaps a formula I can add as a calculated field to get distinct values? I dont need it in a data model other than to get a count distinct which takes out my other options.

I have a bit of a time contraint so I might have to hack something together in SQL. Doesnt see unreasonable to achieve this in Excel does it?

Ok so I found a solution which would work, 1/dividing the count of the field then summing it. which works on a small number if rows. Currently its about 10% done on 100k rows on my 8 cores grrrr
=1/COUNTIF($I$2:$I$100000,I2)

 

[RoryA]

If you can sort the table by order number, you could use a much faster method of entering 1 for the first occurrence and then 0 afterwards. You could then replace those with static values if you need the table in a different order. Or use the view in SQL server to add a unique count field.

 

[maffewBHA]

If you can sort the table by order number, you could use a much faster method of entering 1 for the first occurrence and then 0 afterwards. You could then replace those with static values if you need the table in a different order. Or use the view in SQL server to add a unique count field.

Well my previous method is (almost) finished calculating. I shall copy and paste values for now. I like the idea of your suggestion 1 for the first occurence, then 0's after if its faster! could you suggest how I might do that? I can order the data however I need! i am struggling to even post this reply right now as I dont have any CPU left !