median of calculated median

[blazko]

Hi, 

So I have this problem, I have a table with individual efficiency scores (per day) of each worker. I know how tu calculate a monthly median for each one of them. But, each worker is assigned to a different sector. And what I want to do, is to calculate a monthly median, from a monthly median (score) from every worker assigned to a spesific room (chose by a slicer or filter). So a monthly median for each sector, but calculated from monthly scores of workers assigned to it.

What i have:

- a table with date (daily), worker id, and score 

- a table with workers id's, and sector

Tried various combinations and nothin, either empty result or dax error. Im new to it so the struggle is big. Please help.

[Eric_Zhang]

---

@blazko wrote:

Hi, 

So I have this problem, I have a table with individual efficiency scores (per day) of each worker. I know how tu calculate a monthly median for each one of them. But, each worker is assigned to a different sector. And what I want to do, is to calculate a monthly median, from a monthly median (score) from every worker assigned to a spesific room (chose by a slicer or filter). So a monthly median for each sector, but calculated from monthly scores of workers assigned to it.

What i have:

- a table with date (daily), worker id, and score 

- a table with workers id's, and sector

Tried various combinations and nothin, either empty result or dax error. Im new to it so the struggle is big. Please help.

---

How do you calculate the Median, with a measure? I think there shouldn't be any issue if those two tables are in a proper relationship(one(sector table) to many(daily table)).

[Phil_Seamark]

Hi @Eric_Zhang

I think this is a merge of this one

https://community.powerbi.com/t5/Desktop/median-from-other-median/m-p/152670#M66060

[blazko]

Hi, 

So I have this problem, I have a table with individual efficiency scores (per day) of each worker. I know how tu calculate a monthly median for each one of them. But, each worker is assigned to a different sector. And what I want to do, is to calculate a monthly median, from a monthly median (score) from every worker assigned to a spesific room (chose by a slicer or filter). So a monthly median for each sector, but calculated from monthly scores of workers assigned to it.

What i have:

- a table with date (daily), worker id, and score 

- a table with workers id's, and sector

Tried various combinations and nothin, either empty result or dax error. Im new to it so the struggle is big. Please help.

[Phil_Seamark]

Hi @blazko

Any chance you can post a small sample of your table.  Perhaps 10 or 15 rows with enough for us to try suggest some measures.

[blazko]

date	user	score
2017-01-01	user1	75,00%
2017-01-01	user2	33,48%
2017-01-01	user3	23,04%
2017-01-01	user4	14,64%
2017-01-01	user5	2,11%
2017-01-02	user1	31,13%
2017-01-02	user2	92,83%
2017-01-02	user3	81,63%
2017-01-02	user4	87,79%
2017-01-02	user5	19,45%
2017-01-03	user1	57,24%
2017-01-03	user2	10,11%
2017-01-03	user3	95,28%
2017-01-03	user4	57,79%
2017-01-03	user5	80,25%
2017-01-04	user1	57,17%
2017-01-04	user2	80,83%

And the table with users assigned to sectors:

user	sector
user1	sectoy01
user2	sectoy01
user3	sector02
user4	sector02
user5	se tor02

So I need to calculate a monthly median for each sector, from total monthly medians of each user.

I want to choose month/sector from a slicer/filter

[Phil_Seamark]

Hi @blazko

If I create a relationship between the two tables as follows:

And then create the following calculated measure

Median Score = MEDIAN('Users'[score])

I then can make this Matrix

This might not be 100% right but at least its a starting point

[blazko]

I really hoped itll be that simple, but it calculates in a wrong way:

changed the data a little bit to see the difference in Power BI measures better. What i need is the green, blue calulation. Power Bi with your measure calulates through all the data (light green and orange).

[Phil_Seamark]

Hi @blazko

In your excel sheet.  What is the formula you are using for your green and blue cells?

[blazko]

The basic MEDIAN formula. I could use the table form with the {}, but i dont want to take this calculations in Excel.

I want to use the plain data as in the example inthe first post, and do the measures on the fly, if possible.

[Phil_Seamark · Accepted solution]

Hi @blazko

Please try this formula

Median Score = IF(
			ISFILTERED('Users'[user]),
			CALCULATE(MEDIAN('Users'[score])),
			MEDIANX('Sectors',CALCULATE(MEDIAN('Users'[score])))
		)

[blazko]

Thank You very much!

There is no way I could process it by myself. Works great!