Forum Discussion

erezbenmoshe's avatar
erezbenmoshe
Advocate I
8 years ago
Solved

Count equal values across two rows

                                            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 me...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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
)