MEASURE: Sum/Avg with Condition Question

[ericOnline]

Hello,

I'm trying to create a measure that calculates the rank in one table based on conditions in another table. 

Example:

Here are the tables and relationship:

The measure I have now to calculate Budget Rank is:

Budget Rank = 
   SUMX(
       METRICS, 
       (AVERAGEA(METRICS[BUDGET1_RANK])) +
       (AVERAGEA(METRICS[BUDGET2_RANK]))
    )
    /2

This measure is attached to a simple card on the dashboard.

User clicks a record in the Rollup table (Table visual) and the Card reflects the selected position's budget rank. (This doesn't seem to give reliable results though). 

I need to compare ONLY those with the same Job Title as the selected record. 

Ideally, I need to compare those in the same Dept or Territory as well.
Questions:

- Would I need a separate measure for each Job Title (and each Dept, Territory) or is there a way to set Selected.Job_Title somehow in the measure?

- What would a measure like this look like?

Thank you!

[v-lili6-msft]

hi  @ericOnline 

When you click a record in the Rollup table (Table visual), it will filter other fields in the table visual too, not just Job Title field, so you'd better use a slicer instead it.

And if possible, could you please share your sample pbix file and your expected output? that will be a great help.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[ericOnline]

Hi @v-lili6-msft . The Table selection filters many things on the screen. Its a logical "slicer" to use as it shows the Name/JobTitle/etc. 

I can't share the .pbix file here as it contains private information. 

Any other ideas on how to get this measure put together?

[v-lili6-msft]

hi  @ericOnline 

If possible, could you please use dummy data to create a sample pbix file, that will be a great help.

Regards,

Lin

[ericOnline]

For example, here is the problem I'm running into when using...

Budget Rank = 
   SUMX(
       METRICS, 
       (CALCULATE(AVERAGEA(METRICS[BUDGET_RANK1]), ROLLUP[JOB_TITLE])) +
       (CALCULATE(AVERAGEA(METRICS[BUDGET_RANK2]), ROLLUP[JOB_TITLE]))
    )
    /2

...results in an error showin on the Card with details:

Error Message:
MdxScript(Model) (7, 72) Calculation error in measure 'METRICS'[Budget_Rank1]: Cannot convert value 'Supervisor' of type Text to type True/False.

 How can I write the measure in a generic enough way so that the Job_Title = the selected Job_Title in the Rollup Table vis?

[ericOnline]

Darn! Thought I had it with...

Budget Rank = 
   SUMX(
       METRICS, 
       (CALCULATE(AVERAGEA(METRICS[BUDGET_RANK1]), SELECTEDVALUE(ROLLUP[JOB_TITLE]))) +
       (CALCULATE(AVERAGEA(METRICS[BUDGET_RANK2]), SELECTEDVALUE(ROLLUP[JOB_TITLE])))
    )
    /2

... but this results in the error:

Any other ideas?

[ericOnline]

Here is a related post: https://community.powerbi.com/t5/Desktop/Calculated-Measure-with-conditions/m-p/1084638/highlight/tr...

Basically, how do I create a dynamic filter based on user selection?