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) 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!
Request for support in creation of two measures
Hi, I'm writing with request for support in creation of two measures in my model which is gathering market data from different sources. Market share - brands quantities or values calculated to market eqivalents but providing results equal to 100% on each level, independently from periods (years, quarters, months) and from regions and assigned consultants to them Penetration - above market share of single consultant calculated to average market share for selected brand. I prepared dummy model which I can share. I'd be thankfull for help / tips.
