cancel
Showing results for
Did you mean:
Frequent Visitor

## Dynamic Column calculation based on Slicer Selection

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.

source  |  employeeID  |  date linked  |  Count
google          12345            1/2/2018       1

etc.

IF(
ISBLANK(
), 0, SUM('leads table'[Count])
)

RANKX(
ALLSELECTED( 'employee table' ),

Link Rank Percentile =

Var maxRank =

MAXX (
ALLSELECTED( 'employee table' ),
"Ranks",
RANKX(
ALLSELECTED( 'employee table' ),
),
[Ranks]
)

Return

[Link Rank] / maxRank

Thanks in advance for anyone's help!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic Column calculation based on Slicer Selection

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(
), 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

Pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Dynamic Column calculation based on Slicer Selection

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(
), 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

Pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team

## Re: Dynamic Column calculation based on Slicer Selection

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.