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
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. 🙂
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.
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!
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.
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.
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.
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.
Riny,
Hi, it worked perfectly!!! How did you know to use a Rounddown formula?
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.
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.
Glad I could help! Have a good one yourself as well.
