cancel
Showing results for
Did you mean:
Microsoft

## Get percentage of each status by customer and rank them in a visual

Hi,

I have a single table with a list of customers, and status for each of their leads. What I what to do is to calculate the percentage per customer of each of these statuses like 50% Won and 50% Lost (see example in the picture below). Once I get this percentage for each of the customers, I would like to visually show a ranking of the customers like this:

1. Visual showing top 3 customers by won leads

2. Visual showing top 3 customers by lost leads

For example, in the table below, we can see that A won 33% of his total leads, 33% are still in pending status and 33% are lost.

On the other hand, B won 100% of his leads, while C, who got two leads, won one of them which corresponds to 50%. In the "Top 3 wining %" I would like to see: 1.B - 100%, 2.C - 50%, 3.A - 33%

Thanks for your help. Have a good day!

1 ACCEPTED SOLUTION
Community Support

Hi, @BBrg

According to your description and the sample picture, I can clearly understand your requirement, you can try my steps:

1. Create a calculated column in the data table like this:
``````Percentage =

var _all=

COUNTX(FILTER(ALL('Table'),[Customer]=EARLIER('Table'[Customer])),[Customer])

var _this=

COUNTX(FILTER(ALL('Table'),[Customer]=EARLIER('Table'[Customer])&&[Status]=EARLIER('Table'[Status])),[Customer])

return

DIVIDE(_this,_all)``````

Then change the column format like this:

1. Create a table visual and place fields and apply filters like this:

1. Then do the same thing for another “Losing table” and give them a title, and you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

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

3 REPLIES 3
Community Support

Hi, @BBrg

According to your description and the sample picture, I can clearly understand your requirement, you can try my steps:

1. Create a calculated column in the data table like this:
``````Percentage =

var _all=

COUNTX(FILTER(ALL('Table'),[Customer]=EARLIER('Table'[Customer])),[Customer])

var _this=

COUNTX(FILTER(ALL('Table'),[Customer]=EARLIER('Table'[Customer])&&[Status]=EARLIER('Table'[Status])),[Customer])

return

DIVIDE(_this,_all)``````

Then change the column format like this:

1. Create a table visual and place fields and apply filters like this:

1. Then do the same thing for another “Losing table” and give them a title, and you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

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

Microsoft

Following your formula, I managed to create the "percentage" column and it works fine.

However, When trying to create the visual, and filter for Top 3, I get more than 3 entries, as can be seen below. The first column has customer's names. Any idea why that might happen?

In your pbix file, I can see that it works as expected, instead.

Microsoft

Ah, I think that I know why.

The table shows top 3 percentages (100, 66,67, 50) in the "percentage" column. Not the top 3 customers. hmmm

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors