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.
Hi all,
I have a table that looks similar like the table you can see below. In column "Total" I can see the number of products a customer bought. What I want to know is, how many customers are there who bought products of a different kind (cross-sell > e.g. P1 and P2).
For now I have a formula/measure which gives me back the customers, who bought more than one product : Measure = CALCULATE(DISTINCTCOUNT('Table'[Customer],(FILTER('Table','Table'[Total]>=2)). The result for this example is Measure = 3, but the right result would be 2 (because customer A doesn't count, only bought P1 twice > no cross-sell). What I'm struggling with is a further condition which is testing if the customer bought not only two products, but two different kind of products.
Based on this example, how would the formula look like?
Customer | Total | P1 | P2 | P3 | P4 |
A | 2 | 2 | 0 | 0 | 0 |
B | 3 | 0 | 1 | 1 | 1 |
C | 2 | 1 | 1 | 0 | 0 |
D | 1 | 1 | 0 | 0 | 0 |
Thanks in advance!
Felix
Solved! Go to Solution.
@Anonymous
Still strongly recommend you to change your data format.If you want to keep the current one. you can try below workaround.
create a column
Count =
VAR P1=if('Table'[P1]=0,0,1)
VAR P2=if('Table'[P2]=0,0,1)
VAR P3=if('Table'[P3]=0,0,1)
VAR P4=IF('Table'[P4]=0,0,1)
RETURN P1+P2+P3+P4
create a measure
Measure = CALCULATE(COUNTROWS('Table'),'Table'[Count]>=2)
Proud to be a Super User!
Hi,
You may refer to my solution here - Identify Customers that Organisations can upsell or cross sell their products to
@Anonymous
you can try below step.
1. you don't need total column
2. in PQ, select customer column and unpivot other columns
3. create a measure
Measure =
VAR tbl=SUMMARIZE(FILTER('Table','Table'[Value]>0),'Table'[Customer],"_value",COUNTROWS('Table'))
VAR tbl2=FILTER(tbl,[_value]>=2)
return COUNTROWS(tbl2)
please see the attachment below
Proud to be a Super User!
@ryan_mayu Thanks for your reply!
Is there an other way without unpivoting my table to get the desired result? When I'm following your steps, my other measures and calculations don't work anymore, but I don't want to create a new table besides my main table. So it would be nice to get the result just by using a correct formula for the measure. Is this possible?
Thanks in advance!
@Anonymous
Still strongly recommend you to change your data format.If you want to keep the current one. you can try below workaround.
create a column
Count =
VAR P1=if('Table'[P1]=0,0,1)
VAR P2=if('Table'[P2]=0,0,1)
VAR P3=if('Table'[P3]=0,0,1)
VAR P4=IF('Table'[P4]=0,0,1)
RETURN P1+P2+P3+P4
create a measure
Measure = CALCULATE(COUNTROWS('Table'),'Table'[Count]>=2)
Proud to be a Super User!
Hi @Anonymous ,
In Power Query, you can duplicate or reference your main table (if you need to keep the original data), then perform the following steps on your new table to see how many different products each customer bought.
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYgM4jtWJVnKC8gzhGCTqjCSCUOuCIgIWjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer = _t, P1 = _t, P2 = _t, P3 = _t, P4 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"customer"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "0")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"customer"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
This gives me the following ouput:
You should then be able to relate this to your main table in the model on [customer] to use in visuals/measures.
Pete
Proud to be a Datanaut!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |