Duplicates - Need help with Syntax!

[jl20]

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!

[dedelman_clng · Accepted solution]

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]

Thanks, David. That was extremely helpful!