How do I calculate the 5th percentile value of a measure being used as a value in a matrix?

[mayankpowerbi]

Hello.

I'm struggling with creating a measure in Power BI that gives the 'X' percentile value of a range of values created by another measure that is being used in a matrix.

I have created the matrix below that gives, per customer ID (UniqueIDFinal), the last transaction date (RFM - Last Transaction Date) and the number of days since the last purchase (RFM - Recency Value in Days). The matrix is controlled by the date slicer above. The customer ID, the last transaction date, and the date come from the same table in my model. The number of days since the last purchase is a measure created in the same table.

I want to add a separate measure as a value in the matrix that gives the 5th percentile of the RFM - Recency Value in Days displayed at any given time in the matrix. If the date in the slicer changes, the RFM - Recency Value in Days values will change and so should my intended measure. The measure I want to create should not interact with the UniqueIDFinal value. In other words, if for a given date range, the 5th percentile value is 8 for example, the intended measure should show 8 for all UniqueIDFinal values.

I tried using the following measure, but it doesn't work as expected:

R Score Testing Switch =

PERCENTILEX.INC(

    ALLEXCEPT('Master File','Master File'[Sale Date II].[Date]),

    [RFM - Recency Value in Days],

    0.05

)

It gives an output of 88, whereas it should give an output of 8. The 88 output stays the same regardless of the date I select, which is exactly the opposite of how I want the measure to work. The ALLEXCEPT function isn't working as expected as it is not taking into account the date and looking at the all-time data instead. As mentioned before, it should respond to the date slicer and in this case, give an output of 8.

Any help would be appreciated; I'd like to know if I'm using ALLEXCEPT incorrectly or it is the wrong function entirely. Thank you.

[v-tangjie-msft]

Hi @mayankpowerbi ,

Please try this measure.

R Score Testing Switch =
CALCULATE(
    PERCENTILEX.INC(
        'Master File',
        [RFM - Recency Value in Days],
        0.05
    ),
    ALLSELECTED('Master File'[Sale Date II].[Date]),
    VALUES('Master File'[Sale Date II].[Date])
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[mayankpowerbi]

Hi @v-tangjie-msft thanks for your response.

The measure doesn't work as expected when I insert it into the table.

Here is a screenshot from the Power BI file, which can also be downloaded here. Outside the table, the measure as per your formula provides the correct number, but I would like that same number to be displayed for all UniqueIDs in the matrix. Therefore, R Score Testing Switch should show 29 for all rows in the matrix.

Thanks again in advance.

[v-tangjie-msft · Accepted solution]

Hi @mayankpowerbi ,

Please try it.

R Score Testing Switch = 

CALCULATE(
    PERCENTILEX.INC(
        'Master File',
        [RFM - Recency Value in Days],
        0.05
    ),
   ALLEXCEPT('Master File','Master File'[Sale Date II]))
 

Best Regards,

Neeko Tang

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

[mayankpowerbi]

Hi @v-tangjie-msft hope you're doing well.

I'm trying to replicate the above measure for another value.

In this case, I'm trying to calculate the 'x' percentile value of 'RFM F Value', which measures the frequency or the number of times a customer has made a purchase in the given timeframe.

I have calculated the frequency by counting the number of sale date values per customer. This measure is calcuulated correctly as shown in the RFM F Value values in the matrix. However, when I try and calculate the 5th percentile value as per the previously used logic and formula, the results are unexpected.

No matter what percentile value I choose, the F Score Testing Switch output is always 1, which shouldn't be the case. I would like the F Score Testing Switch output to return the 90th percentile value of the RFM F Value values displayed in the matrix for the given data. As you can see, the formula I have used for this measure is:

CALCULATE(

    PERCENTILEX.INC(

        'Master File',

        [RFM F Value],

        0.90

    ),

   ALLEXCEPT('Master File','Master File'[Sale Date II]))

However, as explained, this is not working as expected.

The link to the Power BI file is given below:

https://www.dropbox.com/s/i4uya0ow1d5co85/Sample%20Power%20BI%20File.pbix?dl=0

Thank you.