Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'd like to be able to classify, and then filter customers in a report based on what they purchased. My customer criteria is:
1. if a customer has only purchased Standard products, then they are a "Standard" customer
2. if a customer has only purchased Classic products, then they are a "Classic" customer
3. if they purchased both Classic and Standard products, then they are a "Both" customer
4. if they purchased a Premium product, then they are a "Premium' customer
In this case, customer A = Standard, customer B = both, customer C = Classic, customer D = Premium. I want to create a calculated column stating the type of customer they are (Standard, Classic, Both, or Premium)
Any suggestions on how to accomplish this? Thank you!
Customer | Product Bought | Customer Type (desired column) |
A | Standard | Standard |
A | Standard | Standard |
A | Standard | Standard |
B | Classic | Both |
B | Standard | Both |
B | Classic | Both |
C | Classic | Classic |
C | Classic | Classic |
D | Premium | Premium |
Solved! Go to Solution.
@Anonymous ,
In such a case better to make it as the column, find the attached file.
Appreciate your Kudos.
@Anonymous ,Please find the attached solution using measures
Thanks @amitchandak! One last thing - how would I create a filter using the Customer Type Recal measure? I want to know how many customers there are, and the breakdown by customer type.
@Anonymous ,
In such a case better to make it as the column, find the attached file.
Appreciate your Kudos.
Hi,
Write this calculated column formula
Category = if(CALCULATE(DISTINCTCOUNT(Data[Product Bought]),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])))=1,Data[Product Bought],"Both")
Hope this helps.
Hi @Ashish_Mathur, thank you for the response. With your solution I was unable to type in a table column after the Earlier function for some reason. Could you also take a look at my edited post and see if your potential solution might be any different than your original post?
Hi,
As you can see, my formula works very well. Mine is a calculated column formula.
@Anonymous ,
try all 4 as measures
all= calculate(distinctCOUNT(table[Product Bought]))
Standard =calculate(distinctCOUNT(table[Product Bought]), Product Bought="Standard")
Classic =calculate(distinctCOUNT(table[Product Bought]), Product Bought="Classic")
flag =
Switch(true(),
[all]=[Standard],"Standard",
[all]=[Classic],"Classic",
"Both"
)
Appreciate your Kudos.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi @Greg_Deckler , thanks for your response! Could you take a look at my modified post? I actually realized I want to create a calculated column called Customer Type (standard, classic, both, or premium). Please let me know if you know of a way to do this in Power BI.
@Anonymous You can create a column like this:
Customer Type =
VAR __Table =
SUMMARIZE(
FILTER(
'Table',
[Customer] = EARLIER([Customer])
),
[Product Bought]
)
RETURN
SWITCH(TRUE(),
"Premium" IN __Table,"Premium",
"Standard" IN __Table && "Classic" IN __Table,"Both",
"Classic" IN __Table,"Classic",
"Standard" IN __Table,"Standard"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |