Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
blazko
Helper III
Helper III

median of calculated median

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.

1 ACCEPTED SOLUTION

Hi @blazko

 

Please try this formula

 

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

 

median4.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
Eric_Zhang
Microsoft Employee
Microsoft Employee


@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)).

 

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

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.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dateuserscore
2017-01-01user175,00%
2017-01-01user233,48%
2017-01-01user323,04%
2017-01-01user414,64%
2017-01-01user52,11%
2017-01-02user131,13%
2017-01-02user292,83%
2017-01-02user381,63%
2017-01-02user487,79%
2017-01-02user519,45%
2017-01-03user157,24%
2017-01-03user210,11%
2017-01-03user395,28%
2017-01-03user457,79%
2017-01-03user580,25%
2017-01-04user157,17%
2017-01-04user280,83%

 

And the table with users assigned to sectors:

 

usersector
user1sectoy01
user2sectoy01
user3sector02
user4sector02
user5se 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

Hi @blazko

 

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

 

Median.png

 

And then create the following calculated measure

 

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

I then can make this Matrix

 

median2.png

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Bez tytułu.jpg

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).

Hi @blazko

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

1.jpg2.jpg

 

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.

Hi @blazko

 

Please try this formula

 

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

 

median4.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thank You very much!

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.