cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## 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!

1 ACCEPTED SOLUTION
Community Champion

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] ) )
)orCnt = 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 2
Community Champion

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] ) )
)orCnt = 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

Helper IV

Thanks, David. That was extremely helpful!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors