Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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?
hi @ericOnline
If possible, could you please use dummy data to create a sample pbix file, that will be a great help.
Regards,
Lin
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?
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |