Show percentile for single customer

[Anonymous]

Hi all, I currently have a Customer View report which gives the ability to filter on only 1 customer. I have the a KPI card showing the customer's revenue, but I want to be able to show that this customer is in the X percentile of customers in terms of revenue. How can I do in this view that given that I am only giving the end user the ability to filter on a single customer at a time for the entire report?

I'd want to have a slicer on the report for Customer, and when I select customer A, show that it is in the 33rd percentile of customers by revenue.

Customer	Revenue
A	20
A	30
B	50
B	60
C	100
C	120

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved? If yes, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,
Lionel Chen

[v-lionel-msft]

Hi @Anonymous ,

Like this?

Measure = 
VAR x = 
CALCULATE(
    SUM(Sheet1[Revenue]),
    ALL(Sheet1)
)
VAR y = 
CALCULATE(
    SUM(Sheet1[Revenue]),
    ALLEXCEPT(Sheet1, Sheet1[Customer])
)
RETURN
DIVIDE(
    y, x
)

Don’t you want the ratio of each customer ’s revenue to total revenue?

Maybe you can list the return values you want to get.

Best regards,
Lionel Chen

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

[Anonymous]

Hi Lionel, no I want the Percentile of the customer in terms of revenue. If I had 10 customers, and used the report slicer to filter on a single customer and this customer's revenue was the highest out of any customer, I want it to show that this customer is in the 100th percentile of customers. If it was the 5th highest revenue customer, I want it to show that this customer is in the 50th percentile.

[AllisonKennedy]

@Anonymous  This should give you percentile;

Percentile =

DIVIDE(RANKX(ALL(DimCustomer), SUMX(RELATEDTABLE(FactInternetSales),FactInternetSales[SalesAmount]),,ASC),COUNTROWS(ALL(DimCustomer)))

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[v-lionel-msft]

Hi @Anonymous ,

Like this?

Sum of revenue = 
VAR x = 
CALCULATE(
    SUM(Sheet1[Revenue]),
    ALLEXCEPT(Sheet1, Sheet1[Customer])
)
RETURN
x

_Rank = 
RANKX(
    ALL(Sheet1),
    [Sum of revenue],
    , DESC, Dense
)

% = 
IF(
    [_Rank] > 1,
    [_Rank] * 0.1,
    [_Rank]
)

If it is still wrong, please list the values of the desired results in a table.

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

Try to do like this:

Measure 4 = 
VAR x = 
CALCULATE(
    SUM('Table'[Revenue]),
    ALL('Table')
)
VAR y = SELECTEDVALUE('Table'[Revenue])
RETURN
DIVIDE(
    y, x
)

Best regards,
Lionel Chen

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

[Anonymous]

@v-lionel-msft , I think this gives me percent of total revenue. I have edited my original post to clarify. I am actually looking for the percentile among the customer base. So if Customer A had the lowest revenue out of the 4 customers, I want the number to show that it is in the 25th percentile of customer base in terms of revenue.

[Anonymous]

Hi @v-lionel-msft , thanks for the response. Var X is giving me total revenue like it should, but Var Y is giving me (blank). Do you know why this is?

[AllisonKennedy]

@Anonymous  My guess for why y is zero would be that you have multiple revenue values for each customer and var y is set only to show the selectedvalue. SELECTEDVALUE in DAX has an optional second argument that gets returned if there is more than one value selected. Try using SUM instead of SELECTEDVALUE

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedy when I do that, the value turns to 0. I think the issue is that for Var X, which is supposed to be the denominator, once the slicer is applied for a customer then Var X turns the same as Var Y thus giving me a 0. I've updated my original post with example data, hope this clarifies my question.

[AllisonKennedy]

You will need to use the ALL() function inside a MEASURE. 

Create a MEASURE that has the calculation for percentile, and when you get to the bottom of the fraction, divide by

CALCULATE(bottom of fraction, ALL())

Or ALL(Customer) depending on if you want the other slicers on the report to affect the total that it divides by.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv