Product Grouping  [Solved]

Anything related to PowerPivot and DAX Formuale
KimberlyJ47
Posts: 4
Joined: Wed Jun 27, 2018 8:47 am

Product Grouping

Postby KimberlyJ47 » Wed Jun 27, 2018 9:36 am

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 (32-bit).
Attachments
product_grouping_question.xlsx
(255.96 KiB) Downloaded 25 times

KimberlyJ47
Posts: 4
Joined: Wed Jun 27, 2018 8:47 am

Re: Product Grouping

Postby KimberlyJ47 » Sat Jun 30, 2018 8:01 am

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 long-term 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 21 times

ozeroth
Posts: 20
Joined: Mon Apr 06, 2015 6:02 am
Location: Auckland, New Zealand

Re: Product Grouping  [Solved]

Postby ozeroth » Sat Jun 30, 2018 1:58 pm

Hi Kimberly,

An idea that sprung to mind when I saw your first post was to create a calculated column in the Customer table that concatenates related values of Product Category. This column will tell you which Product Categories each Customer has in the entire dataset.

The simplest way I can think of is this calculated column in the Customer table:


Or another version that shows "All Categories" when appropriate:


This would give you a dimension to filter Customers by that could be used with any measure.

Modified Excel file attached.

Best regards,
Owen
Attachments
product_grouping_question.xlsx
(276.47 KiB) Downloaded 23 times

KimberlyJ47
Posts: 4
Joined: Wed Jun 27, 2018 8:47 am

Re: Product Grouping

Postby KimberlyJ47 » Tue Jul 03, 2018 4:27 am

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

KimberlyJ47
Posts: 4
Joined: Wed Jun 27, 2018 8:47 am

Re: Product Grouping

Postby KimberlyJ47 » Sat Jul 07, 2018 8:19 am

KimberlyJ47 wrote: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



I tried it with the real data and it worked very well! It's easy to manipulate too as my requirements have changed slightly. Thanks again Owen!

ozeroth
Posts: 20
Joined: Mon Apr 06, 2015 6:02 am
Location: Auckland, New Zealand

Re: Product Grouping

Postby ozeroth » Sat Jul 07, 2018 10:40 am

That's good news , you're welcome! :)


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests