cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bice_cold Frequent Visitor
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.

 

 

leads table:

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

etc.

 

 

Total Links =
IF(
    ISBLANK(
        SUM('leads table'[Count])
    ), 0, SUM('leads table'[Count])
)
 
 
Link Rank =
RANKX(
    ALLSELECTED( 'employee table' ),
    [Total Links],,DESC)
 
 
Link Rank Percentile =

Var maxRank =

MAXX (
    ADDCOLUMNS(
        ALLSELECTED( 'employee table' ),
        "Ranks",
        RANKX(
            ALLSELECTED( 'employee table' ),
            [Total Links],,DESC)
    ),
    [Ranks]
)

Return

[Link Rank] / maxRank
 
Thanks in advance for anyone's help!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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(
        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)

Capture.PNG

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

Capture.PNG

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.
Community Support Team
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.