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

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!