Median - Creating a median for a specific job title vs office in a matrix

[STBristol1981]

Hi all,

I've tried to create a matrix table showing job title (e.g Engineer) on the left vs office across the top.

I then wanted to have the value it pulls up the median for the job title in each office. E.g. 8 engineers in office 1 have a median salary of... and in office 2 the median salary for a hairdresser is...

However, the data that I get is the median for the entire company not that specific role and office.

Any advice welcome.

[fhill · Accepted solution]

We might need to see some fake sample data from your side...  Here I have a normal table, a summary table (to confirm it's Meadian by Office / Position) and the final matrix with normal set-up and no measure or custom coulmns.  Can you show us your fake sample data and how your results turn out?

Forrest

[STBristol1981]

Hi Fhill,

Thank you for showing me that. First time using power BI. 

When I make up a new fictional table the median seems to work and makes sense (except some totals I need to lose). However, when I am pulling in data from multiple sources it can't group them hence the median is one number.

It would be like taking your first table, figuring out the median for the entire list and then when I come to run against office it is saying the median for all them.

I think I need to look at how the data is being drawn in to the tables.

Thank you for your help!

[v-yulgu-msft]

Hi @STBristol1981,

It would be better you can illustrate the scenario with simplified sample data and screenshots of current result you got.

Regards,

Yuliana Gu

[STBristol1981]

Hi Yuliana Gu,

I think the error was on my part in relation to the connection of different excel data. The suggestion further up about starting with simple tables and small really helped. Building up the complexity of the information, checking, building up some more, has helped.

[fhill]

This sounds like possibly a relationship issue, but i'm very much guessing without seeing cleansed data...  Make sure your relationships are set to 'Both' instead of 'Single' under advanced options...  Remember to start small and use simple tables to ensure your linked data is pulling correctly before jumping to end results visuals like matrixies...  Keep us posted, and see below on details on Advanced Relationship settings:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

Cross filter direction

Both - This is the most common, default direction. This means for filtering purposes, both tables are treated as if they're a single table.