Forum Discussion

DA1981's avatar
DA1981
Frequent Visitor
5 years ago
Solved

Coverage calculation

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 clie...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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-NUMBERS/td-p/1904161

     

     

     

DA1981's avatar
DA1981
Frequent Visitor
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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-NUMBERS/td-p/1904161