For each dupllicate in Column A, determine if two specific values 'x' and 'y' appear in column B

[Flawn]

Hello everyone,

I'm trying to produce an interesting report: Column 'a' is essentially a client list. Whenever one of these clients appear multiple times in column a, we need to check if that client has two specific values that occur against them in Column 'b'. Let's call these values 'x' and 'y'.

We then need to calculate the percentage of distinct clients from that list for which both values 'x' and 'y' appear at least once somewhere in column b.

Here's an example:

Clients	Apples or Oranges
John	Apple
Larry	Orange
Sue	Orange
John	Apple
John	Apple
Larry	Apple
Sue	Apple
Larry	Orange
Carl	Apple

For this example we would need to identify that Larry and Sue have both values 'x' and 'y' (apple and orange) in column b - while the others do not have both values.

Therefore, 50% of clients have both the 'x' and 'y' value in column b.

Thanks again in advance for any help, let me know if you need any more info to resolve this. Unfortunately the actual data i'm working with is quite confidential, so I cannot share a real sample.

[amitchandak · Accepted solution]

@Flawn , Create a measure like

Divide(Countx(filter(summarize(Table, Table[Clients],"_1", distinctCOUNT(Table[Apples or Oranges])), [_1] =2), [Clients]), distinctCOUNT(Table[Clients]))

refer this video : https://www.youtube.com/watch?v=X5T4rIZovHk&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=65

[Flawn]

Thanks for the swift answer, but I have a followup that would be very much appreciated: What if we have a third column 'c', and we only want to calculate the percentage of distinct clients from the previous list for which both values 'x' and 'y' appear at least once somewhere in column b AND that have a value in column C.

To expand on the previous example:

Clients	Apples or Oranges	Hungry
John	Apple	Yes
Larry	Orange
Sue	Orange
John	Apple
John	Apple
Larry	Apple	Yes
Sue	Apple
Larry	Orange
Carl	Apple

For this example we would need to identify that Larry and Sue have both values 'x' and 'y' (apple and orange) in column b - while the others do not have both values. Just the same as the previous calculation

But now we have to check column 'c' - Hungry. Because Larry also has at least one row that has a value filled for column C, they are included in the count.

Therefore, 25% of clients have both the 'x' and 'y' value in column b on top of at least one value in column C.

Thanks for expanding on your answer when you have the time. It's very much appreciated.