Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have a table called 'employees' that has employee information. This contains a measure called 'link percentile' that takes the total links an employee accomplishes and assigns them a percentile based on their standing in the organization.
I have another table called 'leads table' that has data tying employees to their lead sources.
The goal is to have a bar graph of counts of sources that can be filtered by the date the link was completed AND the percentile of the employee. I can use a slicer to filter based on the date of the link because it's a static value, but filtering a relative value (top 10% of employees) within that date range is not possible because it's a measure.
The data looks like this:
employee table:
Employee ID | Total Links (measure) | Link Rank | Link Percentile
12345 65 5 1.5%
etc.
leads table:
source | employeeID | date linked | Count
google 12345 1/2/2018 1
etc.
Solved! Go to Solution.
Hi @bice_cold ,
As i know the value in a calculated column will not be dynamic. Here we can create measures and create table visual to work on it.
Total Links = IF( ISBLANK( SUM('leads table'[Count]) ), 0, SUM('leads table'[Count]) )
link rank = RANKX(ALLSELECTED('leads table'),[Total Links],,DESC)
Link Rank Percentile = var _max =MAXX(ALLSELECTED('leads table'),[link rank]) return DIVIDE([link rank],_max)
Pbix as attached.
Regards,
Frank
Hi @bice_cold ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
Hi @bice_cold ,
As i know the value in a calculated column will not be dynamic. Here we can create measures and create table visual to work on it.
Total Links = IF( ISBLANK( SUM('leads table'[Count]) ), 0, SUM('leads table'[Count]) )
link rank = RANKX(ALLSELECTED('leads table'),[Total Links],,DESC)
Link Rank Percentile = var _max =MAXX(ALLSELECTED('leads table'),[link rank]) return DIVIDE([link rank],_max)
Pbix as attached.
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |