How can I cross filter 2 visuals to show lower values

[Dan4]

Hi community, I have created a virtual table from the employee data where I have summarized their level, their job titles and how much each of them earns on average.

I want to create a view whereby when I select a row on "Visual 1", it shows me the table on "Visual 2" filtered.

More specifically I want the table to list the jobs in lower levels that however earn more on average. Hence, as shown in the picture below, when I select on Visual 1 the row "6 Developer 30000", I want Visual 2 to give me a quick view of all the jobs in lower levels that ear more on aaverage (in this case Analyst at leavel 4 earns 35000)
Thank you very much in advance

[bolfri]

Total Pay above selected =
VAR min_level =
    IF (
        ISFILTERED ( 'Datatable'[Level] ),
        CALCULATE ( MIN ( 'Datatable'[Level] ), ALL ( dim_jobs ) ),
        0
    )
VAR min_pay =
    IF (
        ISFILTERED ( 'Datatable'[Level] ),
        CALCULATE ( AVERAGE ( 'Datatable'[Salary] ), ALL ( dim_jobs ) ),
        0
    )
VAR my_level =
    MIN ( dim_jobs[Level] )
VAR my_pay =
    CALCULATE (
        AVERAGE ( 'Datatable'[Salary] ),
        FILTER (
            ALL ( 'Datatable' ),
            'Datatable'[Job_ID] IN VALUES ( dim_jobs[Job_ID] )
        )
    )
RETURN
    IF ( AND ( my_pay > min_pay, my_level < min_level ), my_pay )

[bolfri]

Hi, 

I've created a dim_jobs table that holds all the Job Title and connected them into Oryginal table.

Measures:

Total Pay (Used in the measures)

Total Pay = SUM('Table'[Average Total Pay])

 

Total Pay above selected (Results #1)

Total Pay above selected =
var min_pay = CALCULATE(MINX(VALUES('Table'[Job Title]),[Total Pay]),ALL(dim_jobs))
var my_pay = CALCULATE(SUMX(VALUES('Table'[Job Title]),[Total Pay]),FILTER(ALL('Table'),'Table'[Job Title] in VALUES(dim_jobs[Job Title])))
return if(my_pay>=min_pay,my_pay)

Total Pay below selected (Results #2)

Total Pay below selected =
var max_pay = CALCULATE(MAXX(VALUES('Table'[Job Title]),[Total Pay]),ALL(dim_jobs))
var my_pay = CALCULATE(SUMX(VALUES('Table'[Job Title]),[Total Pay]),FILTER(ALL('Table'),'Table'[Job Title] in VALUES(dim_jobs[Job Title])))
return if(my_pay<=max_pay,my_pay)

and some highlighting measures to help understand the results

Result example 1. Without filtering

 

Result example 2. Analyst filtering

 

Result example 3. Developer filtering

 

I hope this will work well for you as well.

[bolfri]

After some improvements:

Total Pay above selected (level impact included) = 
var min_level = CALCULATE(MINX(VALUES('Table'[Job Title]),MIN('Table'[level])),ALL(dim_jobs))
var min_pay = CALCULATE(MINX(VALUES('Table'[Job Title]),[Total Pay]),ALL(dim_jobs))
var my_level = CALCULATE(SUMX(VALUES('Table'[Job Title]),MIN('Table'[level])),FILTER(ALL('Table'),'Table'[Job Title] in VALUES(dim_jobs[Job Title])))
var my_pay = CALCULATE(SUMX(VALUES('Table'[Job Title]),[Total Pay]),FILTER(ALL('Table'),'Table'[Job Title] in VALUES(dim_jobs[Job Title])))
return IF(ISFILTERED('Table'[Job Title]),if(AND(my_pay>=min_pay,my_level<=min_level),my_pay))

Results:

Example 1. Nothing selected

Example 2. Analyst selected

Example 3. Developer selected

Example 4. Programer selected

You can show only the rows that has higher total pay than selected position by changing the rule:

From:

AND(my_pay>=min_pay,my_level<=min_level)
To:

AND(my_pay>min_pay,my_level<min_level)

By IMO it's good to show what is seleted on the 2nd visual.

[gaylagarrett]

Hi @bolfri ,  I've been following this thread to learn from the answer.  I've tried your solution, but receive an error that the function SUMX cannot work with values of type String.  It seems like the var my_level and var my_pay are trying to sum the Job Title values which are strings.  Do you know how I might have miscopied the formula?  Thank you!

[bolfri]

Did you change the level and pay as a numbers?

[gaylagarrett]

Yup,  that is what I had done.   And I got it to work as @Dan4 wanted. Thank you! 

[bolfri]

If my answer helped and works as you wanted consider accepting it as a solution.

[Dan4]

Thank you so much, however in the latest file you shared, I see that the code has an undesired behavior. When I select Level 3 Architect, the 2nd table shows Level 2 patrol officer with wrong average and it also shows another job title that is in the same level (3). It should show only lower levels with higher average total pay(and the correct total pay).
I have included a screenshot below. Thank you once again for your consideration

[bolfri]

Code after refactoring aka final version on the last sample you gave me:

Total Pay above selected =
VAR min_level =
    IF (
        ISFILTERED ( 'Datatable'[Level] ),
        CALCULATE ( MIN ( 'Datatable'[Level] ), ALL ( dim_jobs ) ),
        0
    )
VAR min_pay =
    IF (
        ISFILTERED ( 'Datatable'[Level] ),
        CALCULATE ( AVERAGE ( 'Datatable'[Salary] ), ALL ( dim_jobs ) ),
        0
    )
VAR my_level =
    MIN ( dim_jobs[Level] )
VAR my_pay =
    CALCULATE (
        AVERAGE ( 'Datatable'[Salary] ),
        FILTER (
            ALL ( 'Datatable' ),
            'Datatable'[Job_ID] IN VALUES ( dim_jobs[Job_ID] )
        )
    )
RETURN
    IF ( AND ( my_pay > min_pay, my_level < min_level ), my_pay )

[smpa01]

@Dan4  PFA, it shows immediately succeding level

[Dan4]

To reiterate on the desired behavior as in the initial description it might not be clear the way I explain it:
if i click on Level 6 Developer on Visual 1, the Visual 2 should show Level 4 Analyst (because the level is lower but the average pay is higher)
if i click on Level 5 Programmer on Visual 1, the Visual 2 should show Level 4 Analyst (because the level is lower but the average pay is higher)
BUT:
if i click on Level 3 Analyst on Visual 1, the Visual 2 should show nothing, since there is no job title with lower level and higher average pay. If there would be a job with higher average pay but also higher level too, it shouldnt show it either becuase the reqirement is lower level&higher average pay
Thanks a lot!

[Dan4]

Thank you so much @smpa01  for taking the time to review my question, however, when I click for example (as shown below) in the left table "Developer 6", it shows me still the same job title "Developer 6" which is incorrect. However it shows right the averagetotalpay of the jobtitle with lower level that has higher average total pay.

The correct solution would be that when clicking on the left table on Level 6 developer, the right table shows Level 4 Analyst

Furthermore, if a job title doesnt have another job title with lower level that earns higher, it shouldnt show anything. In the file you provided it looks like when I click for example on Level 4 Analyst, it shows me the salary of the CEO which is at a higher level which is incorrect. Once again, sincere thanks for your consideration and for taking time in reviewing my question.

[smpa01]

@Dan4  PFA, everything needs to be a measure now, so it returns the immediately preceding lower level  and not the lowest level  When you select 6, you get 4 (immediately preceding lower level) and not 1 (the lowest level).

[Dan4]

Thank you so much @smpa01 , however I see that still if I select Level 5 programmer, the right visual shows a job that is at a higher level (in this case Level 6 Developer). The requirement is that it shows only jobs that are in lower levels but at the same time they are earning more. Thanks a lot and apologies if the requirement is complicated or if I am explaining something unclearly.

[smpa01]

@Dan4  pls check the newest version of the answer. The req was not clear in the beginning, hence so many versions leading to confusion. Should be good now

[Dan4]

sorry if the request is not coming clearly but still when I click on Level 6 developer, it only shows level 4 analyst. It should show all of the jobs that are lower level but earn more. Hence it should also show Level 1 xxxx that it's earning 100 000 000. Thanks a lot for the time you are spending on this. Also, if there is any chance that the suggested solution assumes that there is only one table of employee data, from which there is an aggregation on the levels, job titles and salaries, it would be great as this is what I am tryint to solve for and it would be difficult to create an additional fact table etc. 

[smpa01]

@Dan4  possible with a disconnected dim table, the basic set up is following

PFA

[Dan4]

Thank you so much @smpa01 for taking the time to review this. In the file you provide it works great, however I want to implenent this to the original file where I have the details of employees with real data etc. I have tried to replicate exactly what you have done in the file but it doesnt work. Would it be possible to explain what are the needed measures and the logic behind your solution? Thank you very very much in advance!

[smpa01]

@Dan4 I am currently buried under deadlines on multiple projects till end of Jan. Sorry could not reply you earlier.

The approach here is to show the SUM of ATP of `fact` table in `dim`. Since `dim` has no relationship (disconnected) to `fact` , the only way to show the sum is by this

Now, when you click on the rows of viz in `dim`, you need a mechansim to filter `fact` by that (what is visible).

[Dan4]

Hi @v-chenwuz-msft & @amitchandak  , I have been trying hard to solve this one and tried to come up with a solution similar to the one you suggest here (https://community.powerbi.com/t5/Desktop/Filter-column-ignore-visual-filter/m-p/2428963) however, in this case, it needs to show all of the job titles where the level is lower and the average total pay is higher simultaneously. Whatever I have tried results in when I click on a job title in Visual 1, the exact same job title appears in visual 2.
I managed ot get the number of job titles with lower level and higher average total pay via a measure (as shown below), but the measure gives me only a number and not which ones are these actual job titles. Any help would be higely appreciated.

Measure = Number of jobs in lower levels with higher average pay =

CALCULATE(

    DISTINCTCOUNT(

    'Main data table'[Job title]),

    REMOVEFILTERS('Main data table'),

    'Main data table'[Level]<SELECTEDVALUE('Main data table'[Level]),

    'Main data table'[Average calculation]>SELECTEDVALUE('Main data table'[Average calculation])

)
@bolfri  , @Greg_Deckler , @Idrissshatila , @Ashish_Mathur , @lbendlin