Hello! Merry Christmas to all!
I have a report to submit on Wednesday. I have tried all weekend long to solve this with no luck, thus I'm here hoping someone with more experience has any hints.
The report requires the calculation of the median as one of the measures for a Salary/Wages column given the individual was employed in a specified quarter. The people who were employed are being calculated in Q2Num formula(a count based on dates and employment). I've also tried to add the criteria directly in a FILTER function to the formula below but still, I receive a different Median result if I manually take the salaries in a separate Excel tab and use the Median formula on it. Why is the Median function not calculating correctly in the formula area for my pivot table?
This is the current formula I'm using in the Data Modeling for my pivot table:
Note that the Salary column does have some records that have a null salary or 0 salary. These should be ignored in the median calculation and it's the reason I used the filter function above. Why am I getting different results with the Median function for my pivot table? Am I missing something to get the correct set of Salary records for the median?
Any insight is helpful. I also want to learn from this for the future.
Thank you.
I have a report to submit on Wednesday. I have tried all weekend long to solve this with no luck, thus I'm here hoping someone with more experience has any hints.
The report requires the calculation of the median as one of the measures for a Salary/Wages column given the individual was employed in a specified quarter. The people who were employed are being calculated in Q2Num formula(a count based on dates and employment). I've also tried to add the criteria directly in a FILTER function to the formula below but still, I receive a different Median result if I manually take the salaries in a separate Excel tab and use the Median formula on it. Why is the Median function not calculating correctly in the formula area for my pivot table?
This is the current formula I'm using in the Data Modeling for my pivot table:
Code:
Median:=CALCULATE(MEDIAN(Table1[Salary]),FILTER(Table1,Table1[Salary]>0 && Table1[Q2Num]))Note that the Salary column does have some records that have a null salary or 0 salary. These should be ignored in the median calculation and it's the reason I used the filter function above. Why am I getting different results with the Median function for my pivot table? Am I missing something to get the correct set of Salary records for the median?
Any insight is helpful. I also want to learn from this for the future.
Thank you.
