Forum Discussion

jl20's avatar
jl20
Helper IV
9 years ago
Solved

Duplicates - Need help with Syntax!

Hi all,

 

I am trying to figure out how to calculate the following count and median for a data set with recurring rows. What would the measure syntax be to achieve the results on the bottom right of the image? Thanks in advance!

 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    You can create a summarized table

     

    SumTable = SUMMARIZE(Table2, Table2[ProjNbr], "Revenue", MAX(Table2[Revenue]))

     

    For the count, you don't have to create the table explicitly:

     

    Cnt =
COUNTROWS (
    SUMMARIZE ( Table2, Table2[ProjNbr], "Revenue", MAX ( Table2[Revenue] ) )
)

    or

    Cnt = COUNTROWS (SumTable)

     

    But for the median I don't think you can avoid creating the table as MEDIAN requires a column name

     

    Mdn = MEDIAN(SumTable[Revenue])

     

    Hope this helps.

    David

2 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    9 years ago

    You can create a summarized table

     

    SumTable = SUMMARIZE(Table2, Table2[ProjNbr], "Revenue", MAX(Table2[Revenue]))

     

    For the count, you don't have to create the table explicitly:

     

    Cnt =
COUNTROWS (
    SUMMARIZE ( Table2, Table2[ProjNbr], "Revenue", MAX ( Table2[Revenue] ) )
)

    or

    Cnt = COUNTROWS (SumTable)

     

    But for the median I don't think you can avoid creating the table as MEDIAN requires a column name

     

    Mdn = MEDIAN(SumTable[Revenue])

     

    Hope this helps.

    David

    • jl20's avatar
      jl20
      Helper IV
      9 years ago

      Thanks, David. That was extremely helpful!