Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
megane123
Frequent Visitor

COUNTDISTINCT vs COUNT for coverage calculations

Hi All,

 

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:

 

ClientProduct
A1
A2
A3
B1
B1
B2
C1

 

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 calculated output:

 

ProductClient Coverage
1100%
267%
333.33%

 

Any help would be greatly appreciated.

 

Thank you!

3 REPLIES 3
Anonymous
Not applicable

 

 

[Client Coverage] =
// If more than 1 product is visible
// in the current context it returns
// the minimum coverage among all the
// visible products. So, if 1 product
// is visible, it agrees with your
// definition. If you want this measure
// to return BLANK if many products are
// visible/selected, put the body in
// IF( HASONEVALUE( T[Product] ),...
var AllClientCount =
	CALCULATE(
		DISTINCTCOUNT( T[Client] ),
		ALL( T )
	)
var Result =
	DIVIDE(
		MINX(
			DISTINCT( T[Product] ),
			CALCULATE( DISTINCTCOUNT( T[Client] ) )
		),
		AllClientCount
	)
return
	Result

 

 

Good morning Daxer,

 

Thanks for this! I noticed you replied to my colleague's post with the same query - I had a few questions on your response, hoping you can help:

 

- You have declared AllClientCount but I don't see it used anywhere else in the measure, is this intentional?

- The variable VisibleClientCount isn't declared so will error when trying to run this.

- When you say 'put the body in', where do you mean I should include this in the measure?

 

Thanks v much in advance for your help!

Anonymous
Not applicable

Of course there's a mistake in the code but it's obvious: change the name of the variable that does not exist to the one that does. The above formula is one way to solve the problem. Another one is in the other post you mention.

 

"Put the body in..." means that the whole body of the measure should be wrapped in "IF(..."

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.