cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

## 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 Employee

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 Employee

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors