Subtract a Count measure from MIN per category measure

[av9]

Hi

I am trying to calculate a measure whereby I am subtracting one measure from another.

Sample of Marketing table data:

ContactID	Event	Category
1	click	Website
1	click	Newsletter
2	click	Website
3	click	Article
3	click	Website
3	click	Newsletter
4	click	Newsletter
5	click	Website

The first measure is a count of clicks:

Click Count = CALCULATE (
 COUNT ( 'Marketing'[Contactid] ),
 FILTER (
 'Marketing',
 'Marketing'[Event] = "click"
 ))

The second measure calculates the minimum click count per category.

Min Click Count = VAR ValuesDisplayed =
 CALCULATETABLE (
 ADDCOLUMNS (
 SUMMARIZE ( Marketing, Marketing[Category] ),
 "@Click", [Click Count]
 ),
 ALLSELECTED(Marketing[Category])
 )
RETURN
 MINX ( ValuesDisplayed, [@Click])

I have another measure which subtracts these but I then find the totals when I view it in a table are not totalling correctly.

Adjusted Click Count = [Click Count]-[Min Click Count]

A sample of the final output looks like this table. The totals in the Adjusted click count is not right. Shows as 7 instead of 5.

Category	Click Count	Min Click Count	Adjusted Click Count
Website	4	1	3
Newsletter	3	1	2
Article	1	1	0
Total	8	1	7

Is there another way I can approach this to get the right totals?

[amitchandak · Accepted solution]

@av9 , Try measure like

Adjusted Click Count = sumx(values(Marketing[Category]), [Click Count]-[Min Click Count])

[av9 · Accepted solution]

Thanks I tried this measure and it gives me the correct total 5 but for each row per category its zero. is there a measure tha can do both?

Category	Click Count	Min Click Count	Adjusted Click Count
Website	4	1	0
Newsletter	3	1	0
Article	1	1	0
Total	8	1	5

[amitchandak · Accepted solution]

@av9 , make sure category is coming from Marketing table in visual

if still did not work try like 

Adjusted Click Count =

if( isinscope(Marketing[Category]) , [Click Count]-[Min Click Count] ,

sumx(values(Marketing[Category]), [Click Count]-[Min Click Count])

[CNENFRNL · Accepted solution]

Rewrite the measures in a more concise and logical way,

Click Count = COUNTROWS( Marketing )

Min Click Count = MINX( ALL( Marketing[Category] ), [Click Count] )

Adjusted Click Count = 
SUMX ( DISTINCT ( Marketing[Category] ), [Click Count] - [Min Click Count] )

[amitchandak · Accepted solution]

@av9 , Try measure like

Adjusted Click Count = sumx(values(Marketing[Category]), [Click Count]-[Min Click Count])

[av9 · Accepted solution]

Thanks I tried this measure and it gives me the correct total 5 but for each row per category its zero. is there a measure tha can do both?

Category	Click Count	Min Click Count	Adjusted Click Count
Website	4	1	0
Newsletter	3	1	0
Article	1	1	0
Total	8	1	5

[amitchandak · Accepted solution]

@av9 , make sure category is coming from Marketing table in visual

if still did not work try like 

Adjusted Click Count =

if( isinscope(Marketing[Category]) , [Click Count]-[Min Click Count] ,

sumx(values(Marketing[Category]), [Click Count]-[Min Click Count])

[av9]

Thanks for clarification