Find the Distinct Count of most common day for products used by day of the week (1-7) distinct count

[zgoldflo]

I have bike share data that indicates the day of the week a bike was used in number format 1-7 (indicates days of the week). I want to figure out the most common days of the week a specific route was most popular i.e. for each specific route which (number or) day was most common. I believe what I need to do is a distinct count. I have organized my data into a pivot table for the top ten monthly routes. I want to figure out throughout the month, which day of the week was most common for each route.

I have tried to get a distinct count, but for some reason, that option is grayed out and does not let me select it within the Pivot Table.

Here is the Pivot Table.

_12_2020.xlsx
	H
10
Dec_2020_pt

Here is the source data.

_12_2020.xlsx
	I
7	00:22:34
Dec_2020

Below is an image of the Distinct Count being grayed as an option in my Pivot Table

 

[shift-del]

Hi

Distinct count can only be used with a datamodel pivot table.
But you can't change a normal pivot table into a datamodel pivot table - you have to create it from scratch.

 

[zgoldflo]

Hi. What about the formula or ability to do that from the source data?

Also not sure what the difference is between the two pivot table mentioned above.

 

[shift-del]

what is a data model pivot table - Google Search

 

[zgoldflo]

So change the source data into a table? Then distinct count will do the trick for me?

 

[shift-del]

Are you just guessing?

https://www.myexcelonline.com/wp-content/uploads/2019/04/Data-Model-02.jpg

 

[zgoldflo]

That was a question.

If I change the data source into a Table, create a Pivot Table from there, will I be able to then use Distinct Count to be able to solve the above question I asked?

 

[shift-del]

It seems to me you didn't follow my links and read the articles and viewed the picture.

An Excel table is always a good start but it does not able discount count in a pivot table.