Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BBrg
Microsoft Employee
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%

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

 

BBrg
Microsoft Employee
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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors