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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@fingalbrad , refer if the attached file can help

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.

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

amitchandak_0-1662107243923.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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