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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bilagery
Regular Visitor

Use SUMMARIZE table within weighted median calculation

Hi,

I have the following DAX working CORRECTLY to calculate weighted median. It has two parts: first to create an aggregate table based on ID and ORG, and two calculated columns. Then, try to find the median paid amount based on the summarised table using a quite complex formula.

 

Is there a way that I can simplify this to put the SUMMARIZE function as a temporary thing inside the 'MedianPAID' measure?

 

PaymentSummaryTable = SUMMARIZE('Payment Table',
'Payment Table'[ID],
'Payment Table'[ORG],
"ORG_PAID",SUM('Payment Table'[PAID]),
"ORG_Weight",MIN('Payment Table'[WEIGHTS]))

MedianPAID=(MINX(FILTER(VALUES('PaymentSummaryTable'[ORG_PAID]), CALCULATE(SUM('PaymentSummaryTable'[ORG_Weight]),'PaymentSummaryTable'[ORG_PAID]<=EARLIER('PaymentSummaryTable'[ORG_PAID]))>SUM('PaymentSummaryTable'[ORG_Weight])/2),
'PaymentSummaryTable'[ORG_PAID])
+MINX(FILTER(VALUES('PaymentSummaryTable'[ORG_PAID]), CALCULATE(SUM('PaymentSummaryTable'[ORG_Weight]),'PaymentSummaryTable'[ORG_PAID]<=EARLIER('PaymentSummaryTable'[ORG_PAID]))>(SUM('PaymentSummaryTable'[ORG_Weight])-1)/2),
'PaymentSummaryTable'[ORG_PAID]))
/2

 

UPDATE: Here is the link to the Sample Data with the current set-up.

https://drive.google.com/file/d/1pHba8WtB9plwrn_r6EZeMUFhJEaegH1R/view?usp=sharing

@Ibendlin Thanks for the suggestion!

 

Thank you so much in advance!

 

 

1 REPLY 1
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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