cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Subtract a Count measure from MIN per category measure

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?

4 ACCEPTED SOLUTIONS
Super User

@av9 , Try measure like

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

Helper III

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
Super User

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

if still did not work try like

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

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

Community Champion

Rewrite the measures in a more concise and logical way,

``````Click Count = COUNTROWS( Marketing )

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

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

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
5 REPLIES 5
Community Champion

Rewrite the measures in a more concise and logical way,

``````Click Count = COUNTROWS( Marketing )

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

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

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Super User

@av9 , Try measure like

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

Helper III

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
Super User

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

if still did not work try like

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

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

Helper III

Thanks for clarification

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.