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
croz
Regular Visitor

Calculating Penetration

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)

croz_0-1677151007389.png

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!
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can create a measure like

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

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You can create a measure like

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

Hi John, 

this has worked perfectly!

Thank you very much and have a great day

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.