• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Age Groups and Distinct Count|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Age Groups and Distinct Count|Power Pivot|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotAge Groups and Distinct Count
sp_PrintTopic sp_TopicIcon
Age Groups and Distinct Count
Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
1
July 26, 2022 - 3:25 pm
sp_Permalink sp_Print

Is there a way to do a distinct count in a pivot table with grouping?  I attempted to add pivot table to Data Model for distinct count; however, this will not allow grouping.

Age Groups FEMALE MALE GRAND TOTAL
30-39 1,328 1,508 2,836
40-49 1,041 1,976 3,017
50-59 838 2,774 3,612
60-69 486 2,524 3,010
70-79 41 2,666 2,707
80-89 55 484 539
90-99   119 119

Thank you

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 217
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
July 27, 2022 - 1:32 am
sp_Permalink sp_Print sp_EditHistory

Hi Tyrone!

Use your raw data, age group in rows sex in columns and then you can group your ages by right-clicking > Group > adjust your starting and ending numbers and group by 10. I have attached an example, I assumed you were looking for a count of people in that age group so that's what I have in there, obviously you will want to have this reflect whatever data you actually want to see. Hope this helps!

 

Edit: I just re-read your issue, apparently you would need to write a dax formula to create the groupings and then use that as your rows. I'm not at a computer that has access to the full power pivot data model, but I can play around tonight when I get to another computer if no one has solved this for you by then. 🙂

Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
3
July 27, 2022 - 8:44 am
sp_Permalink sp_Print

Jessica,

Hi, thank you for taking a second look.  We have numerous of patients that are seen in the clinic and some are established patients with follow up appointments.  I want a distinct count of age groups.  If Patient XXX was seen 12 times in the clinics and she is 32 years old female, I do not want to count her 12 times in the age group.  I added data to the "Tyrone_Example" spreadsheet.

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 217
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
4
July 27, 2022 - 5:35 pm
sp_Permalink sp_Print

No workbook attached, be sure to hit 'Start Upload' and wait for the grey check mark after attaching workbook.

I did work on it but had issues and now it's late and I'm tired, forgive me if I start rambling. I found a work around using power query, I don't know if that would be of any help for you. I am more adept at power query than power pivot as I use it more in my work not having access to power pivot at work. Sorry, rambling, this doesn't fully answer your question but maybe it will inspire you with a new idea, I'll keep working on it as well, the answer is there, my creativity is just delayed with the late hour. Happy excel-ling!

Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
5
July 28, 2022 - 11:03 pm
sp_Permalink sp_Print

Jessica,

 

Hi, not an issue. I'm truly grateful you are assisting me. Thank you for letting me know to click on "Start Upload".  I attach the file.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
July 29, 2022 - 12:21 am
sp_Permalink sp_Print

Hy Tyrone,

I believe the attached file contains the type of solution you asked for. When using Power Pivot  you can, indeed, not group row headers like in a regular pivot table. You have to add a column to the table in the DM that takes care of the grouping. The attached file contains your example data and two pivot tables. Both using Distinct Count, though one with details per age. The other with the same totals, but grouped for ages in the 30s through 70s. You'll find two extra columns in the DM. Age Group used a vary basis calculation to concatenate a group like "30-39". Age Group2 is slight more complex, using variables and, thus, less repetitive code. The formulas used were based on Jessica's work in Power query using ROUNDDOWN.

Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
7
July 29, 2022 - 11:07 pm
sp_Permalink sp_Print

Riny,

Hi, thank you for resolving the issue.  I cannot view extra columns in the DM or formulas in Measures because it is a connection only.  When I click on "WorksheetConnection_Tyrone_Example2" only the "Connection Properties" dialog box opens.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
July 30, 2022 - 3:01 am
sp_Permalink sp_Print

Click on the "Power Pivot" ribbon. And then "Manage" on the far left-hand side of the ribbon. That opens the Data model view and you can see the two columns I referred to.

Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
9
July 30, 2022 - 4:33 pm
sp_Permalink sp_Print

Riny,

Hi, it worked perfectly!!!  How did you know to use a Rounddown formula?

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
10
July 30, 2022 - 7:30 pm
sp_Permalink sp_Print

DAX has many functions, and quite a few of them work the same or very similar to the ones in Excel. And why do I know about ROUNDDOWN? I guess because I've used that function before.

Avatar
Tyrone Dames
Member
Members
Level 0
Forum Posts: 12
Member Since:
October 28, 2021
sp_UserOfflineSmall Offline
11
July 31, 2022 - 2:02 am
sp_Permalink sp_Print

Riny,

Again thank you for your valuable input.  I have struggled for three weeks with trying to resolve my issue how to combine an age group and distinct count.  Have a wonderful weekend.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
12
July 31, 2022 - 3:30 am
sp_Permalink sp_Print

Glad I could help! Have a good one yourself as well.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Merav Tzori, Mercedes McCay-Read
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 217
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
David du Toit
leandro barbarini
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27796

 

Member Stats:
Guest Posters: 49
Members: 32326
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.