Calculating percentiles of a measure

[LocationUnknown]

Hi all,

I am currently trying to calculate the quartiles of a measure.

Here is how the data is set up;

Project ID1	Element ID1	Value
Project ID1	Element ID2	Value
Project ID1	Element ID3	Value
Project ID2	Element ID1	Value
Project ID2	Element ID2	Value
Project ID2	Element ID3	Value

From this the value column is then taken to get an adjusted value based on a slicer, this could be A*58 or it could be in the thousands, but this adjustment is applied to all values. Because this needs to update in real time the adjusted values are stored in a measure. Here is the code of that measure:

Benchmark Elemental Value = 
SUM(f_ElementalValues[Value]) *
DIVIDE(
    [Selected Location Factor] * [Selected Time Factor],
    10000
)

Selected Location Factor = 
SELECTEDVALUE(d_LocationFactor[Index], AVERAGE(d_LocationFactor[Index]))

Selected Time Factor = 
SELECTEDVALUE(d_DateFactor[Index], 100)

f_Elemental Values is a fact table which is connected to a few dimesion tables however I do not think they are relevent to the problem. d_ Location and Date factor are non active relationship tables which are filtered with a slicer to have a single value.

I have seen the inbuilt percentile DAX function however this only applies to a column and not a measure. Is there a way someone has found that will calculate the percentile of a measure?

Thanks for any recommendations!

[Anonymous · Accepted solution]

Hi @LocationUnknown ,

The issue here is that PERCENTILE.EXC requires a column. So, what you are going to want to do is create a table as part of your measure in which you want to use PERCENTILE.INC. So, use SUMMARIZE or ADDCOLUMN with a VAR statment to create your "temp table" essentially.

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _ATABLE =
    SUMMARIZE ( 'Table', 'Table'[subject], "VALLUE", [Measure_VALUE] )
RETURN
    PERCENTILEX.INC ( _ATABLE, [VALLUE], .05 )

You could also use Create Table and put your SUMMARIZE or ADDCOLUMN in that and then just reference that table and stuff.

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

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.

[LocationUnknown]

Hi @Anonymous, sadly it did not work and I haven't been able to work out why. I have since scrapped that line of analysis. 

 

[SpartaBI]

@LocationUnknown you have it in the X familty functions:
https://dax.guide/percentilex-inc/
https://dax.guide/percentilex-exc/

Check also the movie tutorials there.

In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
Showcase Report – Contoso By SpartaBI 
Website Linkedin Facebook 
This is SpartaBI!