Update you measure to this:
[Client Coverage (absolute)] =
var AbsoluteTotalClientCount =
// Since Clients is a dimension
// table, each row represents a
// different client, therefore
// countrows will return the
// number of all clients in the
// system. I assume there are no
// referential integrity problems
// in the model. Otherwise, this
// could return a wrong number.
CALCULATE(
COUNTROWS( Clients ),
ALL( Clients )
)
var Result =
AVERAGEX(
DISTINCT( SalesTable[ProductKey] ),
CALCULATE(
DISTINCTCOUNT( SalesTable[ClientKey] )
/ AbsoluteTotalClientCount
)
)
return
Result
Writing DAX measures without the model in sight and without a precise definition is more than hard. When askingfor help, you should always give us all the details.
"In this scenario, I need the Total distinct Client Count to remain at 167, so that the Client Coverages remain as they did in the first table, before the Top N filter is applied."
The above measure will return the absulute coverage but the total SHOULD be what it shows. It shows you the correct number of clients based on the filter. If you want to have a measure that willl always display the total number of clients in your system, you should use a different measure:
[# Clients (absolute)] =
CALCULATE(
COUNTROWS( Clients ),
ALL( Clients )
)
"My second issue is when I try to filter this visual by another column in the dataset (using a slicer), the total Distinct Client Count remains as the actual overall total client count (167), where I need it to be relative to that filter. For example, we have a total of 4 clients in the retail intustry, 3 of which have purchased product A. I would like to show a Client Coverage here of 75% (3/4), but this measure is resulting in a a Client Coverage of 2% (3/167)."
You're talking here about a completely different measure - a relative measure. A measure like this would be:
[Client Coverage (relative)] =
var RelativeTotalClientCount =
// Since Clients is a dimension
// table, each row represents a
// different client, therefore
// countrows will return the
// number of all clients in the
// system. I assume there are no
// referential integrity problems
// in the model. Otherwise, this
// could return a wrong number.
COUNTROWS( Clients )
var Result =
AVERAGEX(
DISTINCT( SalesTable[ProductKey] ),
CALCULATE(
DISTINCTCOUNT( SalesTable[ClientKey] )
/ RelativeTotalClientCount
)
)
return
Result
Also, it's of utmost importance to understand that you should never slice data by fields in your fact table. Only by what's in dimensions. If you don't obey this rule, you'll be in trouble.
