I spent the better part of yesterday trying to figure this out and also did an extensive search through various forums but since the day ended no further than I started I thought I should post myself.
I have a database containing the following 3 tables:
•Customer (lookup table)
•Product (lookup table)
•ProdEnrollments (fact table)
Attached is a sample of the relevant columns in the tables. The real tables contain more columns with other details but this is what I believe to be important for this question
My Question:
In the end, I want to be able to make a graph of product category “groupings” (not product groupings, but product category). For example, how many Baby Boomers have Savings ONLY, how many have Loan ONLY, or how many have BOTH Savings and Loan? The product category groupings I need to use are:
•Savings Only
•Checking Only
•Loan Only
•Savings & Checking
•Checking & Loan
•Savings & Loan
•All 3 Categories
I’m not sure whether to use calculated columns or measures. I think I likely need to use the calculate function but I couldn’t figure out how to set the filters. In the end, I will need to be able to filter or slice the data by generation. Thanks in advance if anyone can help!
I am using Excel 2016 (32bit).
Product Grouping [Solved]

 Posts: 4
 Joined: Wed Jun 27, 2018 8:47 am
Product Grouping
 Attachments

 product_grouping_question.xlsx
 (255.96 KiB) Downloaded 46 times

 Posts: 4
 Joined: Wed Jun 27, 2018 8:47 am
Re: Product Grouping
Update:
I've made some more progress on this. I wouldn't yet call my post "solved" since the solution isn't exactly elegant but I thought I would post what I have so far in case there are others out there encountering the a similar puzzle. Maybe this will help you to think in the right direction.
I ended up using both Calculated Columns and Measures to get to the counts I was looking for:
First, in the Product table, I created several calculated columns using SWITCH() to determine whether or not to include or exclude each product in the measures I would later create. I know I could have used IF(), but SWITCH() seemed a better longterm method in case management asks me to analyze more categories in the future.
For example, lets way I wanted to know if a customer had Checking and/or Savings, my Calculated Column in the Product table was as follows:
CheckingSavingOnly=
SWITCH (
Products[Product Category],
"Checking", "Include",
"Savings", "Include",
"Exclude"
)
Second, I set about creating measures to do the count. I used the EXCEPT() function to include what I wanted and exclude what I didn't want. I also had to use SUMMARIZE() to remove the duplicate customer numbers.
So for example, to calculate customers with Checking Only:
Checking Only:=COUNTROWS(
EXCEPT(
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingOnly])="Include"),
Enrollments[Customer Number]),
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingOnly])="Exclude"),
Enrollments[Customer Number])
)
)
To calculate customers with Checking & Savings Only I used a similar formula, except I had to subtract the Checking Only and Savings Only measures lest they be counted:
C & S Only:=COUNTROWS(
EXCEPT(
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingSavingsOnly])="Include"),
Enrollments[Customer Number]),
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingSavingsOnly])="Exclude"),
Enrollments[Customer Number])
)
)[Checking Only][Savings Only]
I've attached my mockup solution and resulting pivot table. Ideally, I may prefer to put the measures into a singular Calculated Column on the Customer table to make the results easier to work with, but for now this is at least a workable method.
If anyone else has a better solution please let me know!
Blessings,
Kimberly J.
I've made some more progress on this. I wouldn't yet call my post "solved" since the solution isn't exactly elegant but I thought I would post what I have so far in case there are others out there encountering the a similar puzzle. Maybe this will help you to think in the right direction.
I ended up using both Calculated Columns and Measures to get to the counts I was looking for:
First, in the Product table, I created several calculated columns using SWITCH() to determine whether or not to include or exclude each product in the measures I would later create. I know I could have used IF(), but SWITCH() seemed a better longterm method in case management asks me to analyze more categories in the future.
For example, lets way I wanted to know if a customer had Checking and/or Savings, my Calculated Column in the Product table was as follows:
CheckingSavingOnly=
SWITCH (
Products[Product Category],
"Checking", "Include",
"Savings", "Include",
"Exclude"
)
Second, I set about creating measures to do the count. I used the EXCEPT() function to include what I wanted and exclude what I didn't want. I also had to use SUMMARIZE() to remove the duplicate customer numbers.
So for example, to calculate customers with Checking Only:
Checking Only:=COUNTROWS(
EXCEPT(
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingOnly])="Include"),
Enrollments[Customer Number]),
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingOnly])="Exclude"),
Enrollments[Customer Number])
)
)
To calculate customers with Checking & Savings Only I used a similar formula, except I had to subtract the Checking Only and Savings Only measures lest they be counted:
C & S Only:=COUNTROWS(
EXCEPT(
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingSavingsOnly])="Include"),
Enrollments[Customer Number]),
SUMMARIZE(
FILTER(Enrollments,RELATED(Products[CheckingSavingsOnly])="Exclude"),
Enrollments[Customer Number])
)
)[Checking Only][Savings Only]
I've attached my mockup solution and resulting pivot table. Ideally, I may prefer to put the measures into a singular Calculated Column on the Customer table to make the results easier to work with, but for now this is at least a workable method.
If anyone else has a better solution please let me know!
Blessings,
Kimberly J.
 Attachments

 product_grouping_possible_solution.xlsx
 (378.09 KiB) Downloaded 42 times
Re: Product Grouping [Solved]
 Attachments

 product_grouping_question.xlsx
 (276.47 KiB) Downloaded 46 times

 Posts: 4
 Joined: Wed Jun 27, 2018 8:47 am
Re: Product Grouping
Owen,
Thank you! This looks much better than the method I was attempting and would have been way over my head to create these formulas myself! I think I understand what you did, I'll try it out on the real data and then post an update on how it works.
Regards,
Kimberly
Thank you! This looks much better than the method I was attempting and would have been way over my head to create these formulas myself! I think I understand what you did, I'll try it out on the real data and then post an update on how it works.
Regards,
Kimberly
Re: Product Grouping
That's good news , you're welcome!
Who is online
Users browsing this forum: No registered users and 3 guests