Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jl20
Helper IV
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!

 

Capture.JPG

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
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] ) )
)

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

View solution in original post

2 REPLIES 2
dedelman_clng
Community Champion
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] ) )
)

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

Thanks, David. That was extremely helpful!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.