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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soldanr
Frequent Visitor

Median DirectQuery calculation

I am having a hard time calculating the median of a direct query sql table. Usually I would do a column on Power query but I can't use median calculation on power query editor.

 

I would like to calculate the Median for each Part Number and then subtract the calculated median from each operation ID to find the cost difference.

 

I have the following Table:

 

ID   PN   COST

1     A       20

2     B       30

3     A       40

4     B       40

5     A       50

6     B       50

 

and would like the result as:

 

ID   PN   COST    COST-MEDIAN

1     A      20             20-40 = -20

2     B      25             25-30 = -5

3     A      40           40-40 = 0

4     B      30           30-30 = 0

5     A      50           50-40 = 10

6     B      50           50-30 = 20

 

Thank you!

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @soldanr

     After my research, you can do these as below:

Step1:

Add a measure for Median by MEDIAN Function

 

Median = CALCULATE(MEDIAN(Table1[Cost]),ALLEXCEPT(Table1,Table1[PN]))

Step2:

 

Add a measure for COST-MEDIAN

COST-MEDIAN = 
 SUMX(Table1,CALCULATE(SUM(Table1[Cost]))-[Median])

Result:

9.PNG

 here is pbix, please try it.

https://www.dropbox.com/s/7gam0a8mb3gyuuf/Median%20DirectQuery%20calculation.pbix?dl=0

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi, @soldanr

     After my research, you can do these as below:

Step1:

Add a measure for Median by MEDIAN Function

 

Median = CALCULATE(MEDIAN(Table1[Cost]),ALLEXCEPT(Table1,Table1[PN]))

Step2:

 

Add a measure for COST-MEDIAN

COST-MEDIAN = 
 SUMX(Table1,CALCULATE(SUM(Table1[Cost]))-[Median])

Result:

9.PNG

 here is pbix, please try it.

https://www.dropbox.com/s/7gam0a8mb3gyuuf/Median%20DirectQuery%20calculation.pbix?dl=0

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That was perfect! Thank you very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.