Count equal values across two rows

[erezbenmoshe]

I am trying to write a measure that counts the number of [Id] where [Total] is the same for the two [Datatype]s, method1 and method2. 

The count for the data above should be 5.

Thanks!

Erez 

[Ashish_Mathur]

Hi,

Will there only be 2 data types or can there be more as well?

[erezbenmoshe]

Only 2 data types

[Greg_Deckler]

Well, one way to do this would be to use a calculated column like this:

SameTotal = COUNTX(FILTER(Table,Table[Id]=EARLIER(Table[Id]) && Table[DataType]<>EARLIER(Table[DataType]) && Table[Total]=EARLIER(Table[Total])),[Total])

Then you could create a measure that just SUM'd SameTotal or did a COUNT of SameTotal and divided the result of that by the number of methods.

[Zubair_Muhammad · Accepted solution]

@erezbenmoshe

Here is a MEASURE

Measure =
COUNTX (
    FILTER (
        VALUES ( Table1[ID] ),
        CALCULATE ( SUM ( Table1[Total] ), Table1[Data Type] = "Method1" )
            = CALCULATE ( SUM ( Table1[Total] ), Table1[Data Type] = "Method2" )
    ),
    1
)