This is great information. I was wondering if you knew how to take your example a little bit further. In your example, you were able to show how many times the "customerkey" was replicated in the dataset. But is there is way to show how many times "count of customerkey" was replicated. So in short, do exactly what you did to customer key, but do it to "count of customerkey". Currently, I need to take the initial result then copy and paste the result into a new worksheet and create another pivot table? Any help would be greatly appreciated.
fields in the values section get aggregated (e.g., summed, counted, etc). It seems like you want it to count them up in sequence. Maybe try the count functions ruclips.net/video/qlEegBHlPMg/видео.html
Try checking if the pivot table is a regular pivot table or a PowerPivot table b/c the distinct count option in only available. If you are on Excel 2013 or 2010, you would need to download the PowerPivot add in from microsoft.com
this is great, awesome! how can you add other column info to the report count? for example if I need a zip code count report like this (see below) from a large list with multiple columns.Thanks in advance Report Summary ZIP 5 CRRT Description # Records 75033 R002 FRISCO, TX 486 75033 R003 FRISCO, TX 522 75033 R009 FRISCO, TX 424 75033 R016 FRISCO, TX 534 75033 R022 FRISCO, TX 576 75033 R033 FRISCO, TX 551 75033 R034 FRISCO, TX 546 75033 R039 FRISCO, TX 456
It seems like this data needs to be transposed first. See this for some insight ruclips.net/user/dough517search?view_as=subscriber&query=column+transpose
EXACTLY what I was looking for, thank you.
Hi chadd rose, you're welcome!
Thank you very much for posting this video. I was able to help my daughter with her homework.
Thanks Monicapone, glad it helped!
men thank you a lot... you saved me a lot of hours. i spent almost an entire day of work to try getting everything automatic. thank you a lot
Thanks Gonçalo Camões, glad it helped!
Thank you!
You are welcome!
Awesome, much easier method. Thanks for the powerpivot tip.
Hi Julius Edwards, thanks for the comment!
Nice! Thank you!
Thank you too!
Super Solution....Thanks for transferring such knowledge :)
Hi Deepen Singh...you're welcome, glad you liked!
Nice instructions Doug. Thanks so much. Helped a lot.
Glad it helped!
this can be achieved by adding data into data model while preparing pivot and select distinct count
You’re right! One of the many ways Excel gives to do similar/same things!
Thank you so much :)
You're Welcome!
Love it!
Hi Mike, thanks for the comment!
Hands down great video!
Thanks!
Very Helful! . Is it possible set distinc count in an existing pivot? Thanks
Unfortunately not, the source will need to be (re)added to the data model that have distinct count show up for now.
This is great information. I was wondering if you knew how to take your example a little bit further. In your example, you were able to show how many times the "customerkey" was replicated in the dataset. But is there is way to show how many times "count of customerkey" was replicated. So in short, do exactly what you did to customer key, but do it to "count of customerkey". Currently, I need to take the initial result then copy and paste the result into a new worksheet and create another pivot table? Any help would be greatly appreciated.
Countif might fit. See ruclips.net/video/qlEegBHlPMg/видео.html
thank you a lot . i love it
Hi Abdulkareem Alamoudi, thanks for the comment!
Great Sir...
Thanks a lot
You're Welcome!
Hi, how would I show each instance of items rows under row labels. Its summing them all up together. Thanks for any help
fields in the values section get aggregated (e.g., summed, counted, etc). It seems like you want it to count them up in sequence. Maybe try the count functions ruclips.net/video/qlEegBHlPMg/видео.html
Thanks!
Hi irman2779, you're welcome!
I noticed that it is impossible to group when data model is added.
whatif i cant see option of add data to data model?
Thanks a lot
You're Welcome!
Any idea what two do if you wanted the number of customers you have without counting the duplicates using the customer key?
Distinct count? ruclips.net/user/DougHExcelsearch?query=distinct%20count
I'm able to create a power pivot table but Distinct count isn't listed as an option. Do I need to enable something else?
Try checking if the pivot table is a regular pivot table or a PowerPivot table b/c the distinct count option in only available. If you are on Excel 2013 or 2010, you would need to download the PowerPivot add in from microsoft.com
this is great, awesome! how can you add other column info to the report count? for example if I need a zip code count report like this (see below) from a large list with multiple columns.Thanks in advance
Report Summary
ZIP 5
CRRT
Description
# Records
75033
R002
FRISCO, TX
486
75033
R003
FRISCO, TX
522
75033
R009
FRISCO, TX
424
75033
R016
FRISCO, TX
534
75033
R022
FRISCO, TX
576
75033
R033
FRISCO, TX
551
75033
R034
FRISCO, TX
546
75033
R039
FRISCO, TX
456
It seems like this data needs to be transposed first. See this for some insight
ruclips.net/user/dough517search?view_as=subscriber&query=column+transpose