cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soldanr Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Median DirectQuery calculation

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.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Median DirectQuery calculation

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.
soldanr Frequent Visitor
Frequent Visitor

Re: Median DirectQuery calculation

That was perfect! Thank you very much.