Forum Discussion
Solved
Creating customer profiles using existing data
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 |
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.
1 Reply
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.