Client Coverage Measures - not interacting with filters properly

[megane123]

Hi all,

I am having a bit of trouble trying to get filters to properly affect visuals involving coverage measures, specifically the 'Top N' filter and general page slicers.

I am using the below measure to calculate how many distinct clients have purchased a specific product, divided by the total number of distinct clients (167 in this case), resulting in the coverage (%) for each particular product: 

Client Coverage  =

var TotalClientCount =

    CALCULATE(

        DISTINCTCOUNT( SalesTable[ClientKey] ),

        ALL( SalesTable )

    )

var Result =

    AVERAGEX(

        DISTINCT( SalesTable[ProductKey] ),

        CALCULATE( DISTINCTCOUNT( SalesTable[ClientKey] ) )

            / TotalClientCount

    )

return

    Result

This works well when no filters are applied to any of the visuals using the measure, resulting in the following correct results (displayed as a barchart in my dashboard):

Product	Distinct Client Count	Client Coverage
A	130	78%
B	71	43%
C	95	57%
D	56	34%
…	..	..
X	94	56%
Y	26	16%
Z	67	40%
Total Distinct Client Count	167

However, my first issue is when I apply the Top 5 Products (by Client Coverage %) filter to a visual, it skews the total to only look at clients which have ever purchased any of the top 5 products listed (150 in this case), looking like the following (again, displayed by a barchart in my dashboard):

Product	Distinct Client Count	Client Coverage
A	130	87%
B	71	47%
C	95	63%
X	94	63%
Z	67	45%
Total Distinct Client Count	150

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.

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). 

Is there a way to force the measure to display these different filters in the way I have explained? 

Thank you so much in advance for your help!

[Anonymous]

@megane123 

Did my previous post help you achieve what you wanted? If so, please mark it as THE solution. Thanks.

[Anonymous]

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.

[amitchandak]

@megane123 , if yoi want filter to work try allselected

Client Coverage =
var TotalClientCount =
CALCULATE(
DISTINCTCOUNT( SalesTable[ClientKey] ),
ALLSELECTED( SalesTable )
)
var Result =
AVERAGEX(
DISTINCT( SalesTable[ProductKey] ),
CALCULATE( DISTINCTCOUNT( SalesTable[ClientKey] ) )
/ TotalClientCount
)
return
Result