cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BBrg
Microsoft
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%

BBrg_1-1615996781968.png

 

Thanks for your help. Have a good day!

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1616143624428.png

 

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

v-robertq-msft_1-1616143624443.png

 

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

v-robertq-msft_2-1616143624446.png

 

You can download my test pbix file here

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1616143624428.png

 

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

v-robertq-msft_1-1616143624443.png

 

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

v-robertq-msft_2-1616143624446.png

 

You can download my test pbix file here

 

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.

Hi @v-robertq-msft ,

 

Thanks so much for your detailed answer.

 

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.

 

BBrg_0-1616162839984.png

 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors