Dynamic measure calculations using page filter

[fingalbrad]

HI 

I have a group of notifications like this

Asset Model       Failure Name        Condition Rating     Total Risk   Work Centre  Notification NUmber

A                               1                             10                          5               A                          12

A                               1                              10                         7               B                           13

A                                1                             10                        8                A                           14

A                               2                               9                         5                 A                          15

A                                2                              10                      7                  A                          16

B                               3                                5                        8                 A                          17

C                               4                                6                        9                 A                          18

C                                 4                              6                       5                  A                          19

What I want to do is create a table that calculates the median risk for each Asset Model - Failure Name - Current Condition Rating combination. There is a slicer on the page which is Work Centre.I want this table to be able to be dynamic and change when the slicer is changed between work centre. So the table summarises all the data, but if someone chooses work centre A, then only those notifications are considered and the median is calculated for those. The hard part is, I want these combinations to be ranked. I also want this ranking to be plottable. So far I have been able to create the table by just brnging in the columns and I created a measure :

Median Total Risk = median('JP Outputs'[Total Risk])

I then created a rank measure which is:

Median Risk Rank = rankx(ALLSELECTED('JP Outputs'),calculate('JP Outputs'[Median Total Risk]))

The thing is this gives me a ranking as shown:

It does not start at 1.  So that is my first issue. 

Then When I go to plot it as a scatter plot I put in these as my inputs

 and I get a plot like this

This is ranking every notification. But I want them ranked via the combination and the combination median risk rank

So In rank 1 it would be  the 2 notifications for crossarm tilted and their individual total risk outputs plotted in the y axis

in rank 2 it would be one notification for surface degraded etc

And I want these to dynamically change an recalculate when I change the slicers for the work centre

[Anonymous]

Hi there,

I think you might need your ranking to be based on a column not a table which it appears to be. I have used rankings where I am ranking countries by spend here is my formula

If(ISINSCOPE(Countries[International Short]),If(Not ISBLANK([Dynamic Spend ($M)]),RANKX(ALLSELECTED(Countries[International Short]),Combined[Dynamic Spend ($M)])))

If you want to rank by the combination of your 3 columns (asset model, failure name, condition rating) I would recommend creating a column which concatenates all 3 together and then use that where I have 'Countries[International Short]'. Replace my '[Dynamic Spend ($M)]' with your Median Total Risk.

Hope this helps.

(P.S. I am just a fellow end user so apologies if my response isn't useful)