Inter quartile range of a measure with dynamic filtering

[Joshwald]

I'm trying to calculate the maximum, median and xth percentile ranges of a measure that do not change with filtering on a certain field.

My data looks like:

Development Months	Item	Measure
1	A	0.1
1	B	0.2
1	C	0.15
2	A	0.25
2	B	0.3
2	C	0.35
3	A	0.4
3	B	0.4
3	C	0.55
4	A	0.55
4	B	0.5
4	C	0.75
5	A	0.7
5	B	0.6
5	C	0.95

With the current graphs produced being:

I want to add the following calculations on the measure:

Development Months	Item	Measure	Max at Development Month	Median	25th percentile	75th percentile
1	A	0.1	0.2	0.15	0.125	0.175
1	B	0.2	0.2	0.15	0.125	0.175
1	C	0.15	0.2	0.15	0.125	0.175
2	A	0.25	0.35	0.3	0.275	0.325
2	B	0.3	0.35	0.3	0.275	0.325
2	C	0.35	0.35	0.3	0.275	0.325
3	A	0.4	0.55	0.4	0.4	0.475
3	B	0.4	0.55	0.4	0.4	0.475
3	C	0.55	0.55	0.4	0.4	0.475
4	A	0.55	0.75	0.55	0.525	0.65
4	B	0.5	0.75	0.55	0.525	0.65
4	C	0.75	0.75	0.55	0.525	0.65
5	A	0.7	0.95	0.7	0.65	0.825
5	B	0.6	0.95	0.7	0.65	0.825
5	C	0.95	0.95	0.7	0.65	0.825

With the desired graph:

Noting the Max/MED/Xth Percentiles do not change if you filter away A, B or C.

Thanks

Josh

[v-rongtiep-msft]

Hi @Joshwald ,

Do you mean that the values on the segment graph change when the slicer is selected? In fact, the value does not change, but the interval interval of the Y axis changes with the value. 

You can compare the interval between two changes, or you can see if the value of C changes.

For current Power BI, it is impossible to custom the interval on any axis. You may vote on these ideas:

Add the axis interval in the charts 

Axis with configurable units (/Interval) 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joshwald]

Thanks Polly

Not quite, the issue is with the Max/Median/percentile measures in the ToolTip of the graphs. You see the values are not correct for each interval. i.e. the Max at development month 3 is 200%. This change happens when I set measures 1-3 to the formulae used in my pbix file as opposed to the measures you created.

Is there a way for the Max/Median/Percentile measures to work as they did in your example but on the measures in my updates example?

Thanks

Josh

[v-rongtiep-msft]

Hi @Joshwald ,

Can't  use the measure I created directly?

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joshwald]

Unfortunately not, they don't quite give the same results as the DAX in the updated measures in my dataset. Is there a way to achieve the same results as the Max/Median/Percentiles with those measures?

Thanks

Josh

[Joshwald]

Hi there - I have reposted the excel link (which you should be able to download now)

Thanks

Josh

[Joshwald]

https://docs.google.com/spreadsheets/d/1rt8rKCx6_PkzVyearUCIf5mAgE46tXJv/edit?usp=sharing&ouid=10931...

[v-rongtiep-msft]

Hi @Joshwald ,

Please refer to my pbix file.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joshwald]

Thank you very much for your help. Unfortunately I'm still seeing the same issue.

I've updated your file to use the measure formulae I have in the dashboard, you can then see the Max/Medium/Percentile measures misbehaving. 

https://drive.google.com/file/d/1B3QfpRtV2AUnX2PIfCjGLdyyCec8iYW4/view?usp=drivesdk

[Joshwald]

Thank you for the post. Unfortunately this doesn't return what I need. The median calculated is the same as the measure for A, B and C i.e. doesn't calculate the median of all A, B and C but rather of A and B and C individually.

I've attached a sample data set and desired graph (note this should show the points of the Max, Median, 25th and 75th percentiles at each development month, however opening in google sheets breaks the formulae. It works in Excel).

DataSample in Excel 

[v-rongtiep-msft]

Hi @Joshwald ,

I cannot download the file,  please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft]

Hi @Joshwald ,

I have created a simple smaple, please refer to it to see if it helps you.'

Create measures.

25th =
PERCENTILEX.INC (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Development Months] = SELECTEDVALUE ( 'Table'[Development Months] )
    ),
    [MMEASURE],
    0.25
)

75th =
PERCENTILEX.INC (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Development Months] = SELECTEDVALUE ( 'Table'[Development Months] )
    ),
    [MMEASURE],
    0.75
)

Median =
MEDIANX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Development Months] = SELECTEDVALUE ( 'Table'[Development Months] )
    ),
    [MMEASURE]
)

 The [MMEASURE] is the [Measure] in your table.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.