Calculating Penetration

[croz]

Hi all,

I was wondering if anyone could help me with a dax/Powerbi problem,

im currently trying to work out the penetration between 2 columns

i have a matrix which consits of two 3 column 'ClientID', 'PolicyID' and 'Penetration' (note the 'ClientID' and 'PolicyID' are both DISTINCTCOUNT measures)

and two fields for the rows are 'Department' which then exapands into 'Categories'. (London is Department and the 5 values are the Categories)

(see table for example)

to work out the penetration i need to divide the values in 'Dis PolicyID #' with the total of 'Dis ClientID#'  depending on the 'Category/Department' so for example, in the 'category' field under 'Department', the 'Activies' row has 10 policies, i then need to divide 10 by 3322 to get the penetration for 'Activities'. Furthermore, for 'Excercise' i would have to divide 116 by 3322 to get the penetration for 'Exercise'.

Does Anyone know the calcuation for this? 

i currenty have a few examples but none are displaying the right answer. 

Measure 4 = DISTINCTCOUNT(tablePolicy ID]) / SUMX(DISTINCT(table[Category]), DISTINCTCOUNT(table[Category]))

or 

Measure 4 = DISTINCTCOUNT(tablePolicy ID]) / SUMX(DISTINCT(table[ClientID]), DISTINCTCOUNT(table[ClientID]))

Many thanks!

[johnt75 · Accepted solution]

You can create a measure like

Penetration = 
VAR NumClients = CALCULATE( [Dis Client], ALLSELECTED('Table'[Category] ) )
VAR NumPolicies = [Dis Policy]
RETURN DIVIDE( NumPolicies, NumClients )

[croz]

Hi John, 

this has worked perfectly!

Thank you very much and have a great day