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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
fingalbrad
Helper I
Helper I

Calculate median and median rank for a combination and change for filter

HI I have this data set

Asset ClassAsset ModelFailure CodeNotification NumberCondition RatingTotal RiskWork Centre
1110$10aa
aa229$11bb
aa238$12cc
bb3410$13aa
bb3510$14aa
bb3610$15aa
cc475$16aa
cc485$17aa
cc495$18bb
dd5106$19aa
dd5116$20bb
dd5126$21aa

 

I want to create this table 

 

Asset ClassAsset ModelFailure CodeCount of NotificationsCondition RatingMedian RiskMedian Risk Rank
1110calculate thiscalculate this
aa219calculate thiscalculate this
aa218calculate thiscalculate this
b3310calculate thiscalculate this
c435calculate thiscalculate this
dd536calculate this

calculate this

 

The thing is, I want to be able to have a slicer on the page and if I change the work centre it recalculates the median for the ones in that work centre and the rank as well.

 

Then, what I want to do is plot a scatter plot and have the rank on the x axis of a scatter plot and the total risk on the y axis for each notification and this changes when you filter on work centre. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@fingalbrad , refer if the attached file can help

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@fingalbrad , refer if the attached file can help

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi I see your formula was calculating the median risk for Asset Class. I wanted to do it for a combination of Asset Model ID & Failure Code & Condition Rating so I made a combination of those as another column then passed that through and it seemed to work. Now when I do it in my report, the median seems to be working but the rank doesnt. 

 

fingalbrad_0-1662008310056.png

I then created some measures 

 

fingalbrad_1-1662008455308.png

and then I created the rank

fingalbrad_2-1662008505907.png

 

but for some reason the AM/FM Risk one works and I can put it in a table

 

fingalbrad_3-1662008562326.png

But when I try and add Rank AM/FM Risk it goes blank. Can you see what I have done wrong??

 

 

 

@fingalbrad ,
Can you share sample pbix after removing sensitive data.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@fingalbrad , I tried on old data, but it is working

amitchandak_0-1662107243923.png

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

you are an absolute legend. That works perfectly. Thank you so much for that

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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