Coverage calculation

[DA1981]

Hello everbody,

I am struggling to work out what I think should be some fairly straightforward DAX and can't find anything in the forum. Apologies if I have missed another post.

I have a client and product columns. What I am trying to achieve is to calculate what 'client coverage' each product has. Client coverage is defined by the percentage of clients that have a product and is between 0% and 100%. What is throwing a spanner in doing a COUNT(Products)/COUNT DISTINCT(Clients) is that some clients have some products more than once; the way I want my calculation to work is to be binary: A client has it or doesn't. I am not interested in how many times they have bought it.

Example data:

Client	Product
A	1
A	2
A	3
B	1
B	1
B	2
C	1

In this instance, all clients have product 1, but Client B has it twice. Therefore coverage should be 100% not 133% if I did the formula above.

Expected caluculated output:

Product	Client Coverage
1	100%
2	67%
3	33%

Any help much appreciated. Thanks in advance.

[Anonymous]

I remember having solved this problem on this forum. Where's the solution???

[DA1981]

Hi. Thanks for the response. This was posted twice in error so you replied to the other post.

And you didnt solve it, which is why I never accepted a solution. You declared 2 variables and only used one of them, which made no sense.

[Anonymous · Accepted solution]

This is something similar to what I've posted before:

[Client Coverage] =
var TotalClientCount =
	CALCULATE(
		DISTINCTCOUNT( T[Client] ),
		// Using allselected here means
		// that the results will be relative
		// to the currently visible/selected
		// set of clients. If you want to
		// get the ratios in absulute terms,
		// change this to ALL( T ).
		ALLSELECTED( T )
	)
var Result =
	AVERAGEX(
		DISTINCT( T[Product] ),
		CALCULATE( DISTINCTCOUNT( T[Client] ) )
			/ TotalClientCount
	)
return
	Result

By the way, this solution comes with a huuuuuge warning: You should never use single-table models in production. If you want to know one of the many reasons why not, please check this out: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-one-table-models-will-produce-WRONG-NUMBE...

[Anonymous]

@DA1981 

I did solve it. I even created a quick solution in PBI to convince myself that it did return what you expected. Can you please find the link to the other post and place it here? Or if not, do you have the solution I gave you somewhere?