Calculate percentile between tables

[atorliga]

Hi all,

I have two related tables in my Power BI model:

📁 Table 1: "Reportes uso ChatGPT"

Contains usage data per employee:

• "Fecha" (Date in dd/mm/yyyy, representing the month)

• "email" (employee's corporate email)

• "messages" (number of ChatGPT iterations that user performed in that month)

➡️ A user can appear multiple times (one row per month of usage).

---

📁 Table 2: "Datos demográficos"

Contains unique employee information:

• "ID" (employee identifier)

• "Dirección email trabajo" (corporate email address)

➡️ This table has one row per employee.

---

🔗 Relationship:

There's a one-to-many relationship:

• From "Datos demográficos"[Dirección email trabajo]

• To "Reportes uso ChatGPT"[email]

---

✅ What I need:

I want to create a DAX measure that calculates the percentile of each employee based on their total ChatGPT usage (messages), across all employees in the company, including those who:

• have no usage at all,

• have never appeared in the usage table,

• or have zero messages.

The idea is that:

• Employees with more messages get a higher percentile (100% = highest usage),

• Employees with less or no usage get lower percentiles (down to 0% = no usage).

---

💡 Ideally:

I’d like to do it in separate DAX measures so I can use it for other measures:

1. Total messages per employee

2. Ranking among all employees (based on total messages)

3. Percentile normalized between 0 and 100

---

📌 Note: I'm open to using TREATAS, RANKX, or virtual tables (ADDCOLUMNS, SUMMARIZE, etc.) if necessary.

---

Any ideas or recommended approach to achieve this?

Thanks in advance! 🙏

[v-pnaroju-msft]

Hi atorliga,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution, as this helps the broader community.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi atorliga,

We wanted to check in and see if the information we provided helped resolve your issue. If you require further assistance, please don’t hesitate to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi atorliga,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @bhanu_gautam, for your response.

Hi atorliga,

We appreciate your inquiry shared through the Microsoft Fabric Community Forum.

Based on our understanding of the issue, we have reproduced the scenario using a sample dataset and successfully obtained the expected output. In this output, all employees, including those without usage records, are accurately displayed with Total Messages, Rank, and Percentile Score (ranging from 0 to 100) that responds to slicers like date. We have used a combination of CALCULATE, SELECTEDVALUE, COALESCE, Calendar logic and RANKX functions to achieve this.

Please find attached a screenshot and a sample PBIX file for your reference:

We hope that the information provided will assist in resolving the issue.If you find our response helpful, kindly mark it as the accepted solution. This will assist other community members facing similar queries.
Should you have any further queries, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[bhanu_gautam]

@atorliga 

DAX
TotalMessagesPerEmployee =
CALCULATE(
SUM('Reportes uso ChatGPT'[messages]),
ALLEXCEPT('Reportes uso ChatGPT', 'Reportes uso ChatGPT'[email])
)

DAX
EmployeeRank =
RANKX(
ALL('Datos demográficos'),
[TotalMessagesPerEmployee],
,
DESC,
DENSE
)

DAX
EmployeePercentile =
VAR TotalEmployees = COUNTROWS(ALL('Datos demográficos'))
VAR EmployeeRank = [EmployeeRank]
RETURN
DIVIDE(EmployeeRank - 1, TotalEmployees - 1, 0) * 100

[atorliga]

Thanks a lot for the help so far — really appreciate the guidance!

I just want to clarify the full requirement to make sure the DAX solution is aligned with my scenario:

---

✔️ I have a full list of employees in a table called 'Datos demográficos'.
Some of them do not have a ChatGPT license, and therefore do not appear at all in the usage table 'Reportes uso ChatGPT'.

✔️ Still, I need all employees — including those with no usage data — to appear in the calculation of percentiles:

• Ideally with a 0 or BLANK() message count,

• And placed at the bottom of the ranking, corresponding to the lowest percentiles (0 %).

✔️ In addition, I want this to work dynamically with time-based filters:

• If I use a slicer for a specific month, the total messages should reflect only that month.

• The percentile must then rank employees based on usage in that period, but still include all employees, even those who had no activity that month.

---

📊 Goal:

• A dynamic, context-aware percentile measure for each employee

• Based on their total messages (per current filter context)

• Over the full employee base from 'Datos demográficos'

• That works with date slicers and other filters in the report

---

Thanks again for the help — any working DAX examples or performance tips would be amazing! 🙏