Forum Discussion

DA1981's avatar
DA1981
Frequent Visitor
5 years ago
Solved

Coverage calculation

Hello everbody,   I am struggling to work out what I think should be some fairly straightforward DAX and can't find anything in the forum. Apologies if I have missed another post.   I have a clie...
  • Anonymous's avatar
    Anonymous
    5 years ago

    This is something similar to what I've posted before:

     

    [Client Coverage] =
    var TotalClientCount =
    	CALCULATE(
    		DISTINCTCOUNT( T[Client] ),
    		// Using allselected here means
    		// that the results will be relative
    		// to the currently visible/selected
    		// set of clients. If you want to
    		// get the ratios in absulute terms,
    		// change this to ALL( T ).
    		ALLSELECTED( T )
    	)
    var Result =
    	AVERAGEX(
    		DISTINCT( T[Product] ),
    		CALCULATE( DISTINCTCOUNT( T[Client] ) )
    			/ TotalClientCount
    	)
    return
    	Result

    By the way, this solution comes with a huuuuuge warning: You should never use single-table models in production. If you want to know one of the many reasons why not, please check this out: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-one-table-models-will-produce-WRONG-NUMBERS/td-p/1904161