Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi, @BBrg
According to your description and the sample picture, I can clearly understand your requirement, you can try my steps:
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:
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, @BBrg
According to your description and the sample picture, I can clearly understand your requirement, you can try my steps:
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:
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |