CALCULATE and DISTINCTCOUNT with condition

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may refer to my solution here - Identify Customers that Organisations can upsell or cross sell their products to

[ryan_mayu]

@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

[Anonymous]

@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!

[ryan_mayu · Accepted 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)

[BA_Pete]

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