cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.