Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.