Forum Discussion
Solved
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 ca...
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:
here is pbix, please try it.
https://www.dropbox.com/s/7gam0a8mb3gyuuf/Median%20DirectQuery%20calculation.pbix?dl=0
Best Regards,
Lin
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:
here is pbix, please try it.
https://www.dropbox.com/s/7gam0a8mb3gyuuf/Median%20DirectQuery%20calculation.pbix?dl=0
Best Regards,
Lin
That was perfect! Thank you very much.
