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 method2. 

 

The count for the data above should be 5.

 

Thanks!

Erez 

  • 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
)

4 Replies

  • 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
)