Creating customer profiles using existing data

[rrjr007]

I'd like to use Power BI to create customer profiles based on rules.  I do this is excel and it is a huge time crucnh.  I'm hoping this is soemthing I can do.

Below is my data:

Company X	Apples	Brand 1	1
Company X	Apples	Brand 2	24794
Company X	Apples	Brand 3	3472
Company X	Apples	Brand 4	14
Company X	Apples	Brand 5	108
Company X	Apples	Brand 6	53
Company X	Apples	Brand 7	6907

The first thing i do is calcualte what % of apples company x purchases from various brands

Company X	Apples	Brand 1	1	0.003%
Company X	Apples	Brand 2	24794	70.141%
Company X	Apples	Brand 3	3472	9.822%
Company X	Apples	Brand 4	14	0.040%
Company X	Apples	Brand 5	108	0.306%
Company X	Apples	Brand 6	53	0.150%
Company X	Apples	Brand 7	6907	19.539%

Next, I create my profiles:

Advocate	100%
Loyalist	75% to 99%
Primary	40% to 74%
Secondary	11% to 39%
Tertiary	5% to 10%
Trialist	1% to 4%
None	0%

My output should look like the table below.  Can I do this in power bi?  Thoughts on how?

Thanks in advance.

COMPANY	FRUIT	# PURCHASED	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5	Brand 6	Brand 7
Company X	Apples	35349	None	Primary	Tertiary	None	None	None	Secondary

[v-eachen-msft · Accepted solution]

Hi @rrjr007 ,

Here are two ways.

1.Use DAX

Per = 
DIVIDE('Table'[# PURCHASED],CALCULATE(SUM('Table'[# PURCHASED]),ALLSELECTED('Table')))

Sum =
CALCULATE(SUM('Table'[# PURCHASED]), ALLSELECTED('Table'))

Then you could use IF() function to show final results.

2. Use m query
Open advance editor, and refer to the following m query:

let
    Source = Your source,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
    Custom1 = #"Changed Type",
    #"Renamed Columns" = Table.RenameColumns(Custom1,{{"Column1", "Company"}, {"Column2", "Fruit"},{"Column3", "Brand"},{"Column4", "# PURCHASED"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Fruit"}, {{"Count", each List.Sum([#"# PURCHASED"]), type number}, {"sum", each _, type table [Company=text, Fruit=text, Brand=text, #"# PURCHASED"=number]}}),
    #"Expanded sum" = Table.ExpandTableColumn(#"Grouped Rows", "sum", {"Company", "Fruit", "Brand", "# PURCHASED"}, {"sum.Company", "sum.Fruit", "sum.Brand", "sum.# PURCHASED"}),
    #"Added Custom" = Table.AddColumn(#"Expanded sum", "Custom", each if
[#"sum.# PURCHASED"]/[Count] >=0.01 
and
[#"sum.# PURCHASED"]/[Count] <0.05
then
"Trialist"
else if 
[#"sum.# PURCHASED"]/[Count] >=0.05 
and
[#"sum.# PURCHASED"]/[Count] <0.11
then
"Tertiary"
else if 
[#"sum.# PURCHASED"]/[Count] >=0.11 
and
[#"sum.# PURCHASED"]/[Count] <0.4
then
"Secondary"
else if 
[#"sum.# PURCHASED"]/[Count] >=0.4 
and
[#"sum.# PURCHASED"]/[Count] <0.75
then
"Primary"
else if 
[#"sum.# PURCHASED"]/[Count] >=0.75 
and
[#"sum.# PURCHASED"]/[Count] <0.99
then
"Loyalist"
else if 
[#"sum.# PURCHASED"]/[Count] = 1
then
"Advocate"
else 
"None"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"sum.# PURCHASED"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[sum.Brand]), "sum.Brand", "Custom")
in
    #"Pivoted Column"

Here is the result.

Here is my test file for your reference.

Table is created by DAX, Table2 is created by m query.