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 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 |
Solved! Go to 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.
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.
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |