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.
I am looking to create a measure that shows the pecentage of column A within Column B (the total project cost), with column A adjusting depending on the filters applied from slicers. I am then looking to average over these percentges to come up with a KPI as to the average level of the given filter per project. Is there a way to have in 1 step the average of a series of percentages as a measure.
I have included an example below:
Dynamice Measure: adjusts to filter: = SUMX('Cost'[Spend]')
Un-Dynamic; remains static per row: =SUM('Project'[Total Cost])
My Measure currently divided Dynamice Measure / Un-Dynamice Measure, this is fine per row but results in the wrong total, which is an aggregation rather than an average. Any recommendations?
Project | Dynamic Resource Measure | Un-Dynamic Measure | Measure Test |
Project 1 | £119,270 | £164,153 | 72.66% |
Project 2 | £1,310,369 | £1,880,506 | 69.68% |
Project 3 | £197,347 | £291,042 | 67.81% |
Project 4 | £803,032 | £1,373,913 | 58.45% |
Project 5 | £1,449,017 | £2,703,286 | 53.60% |
£3,879,034 | £6,412,900 | 60.49% | |
Average | 64.44% |
Solved! Go to Solution.
Hi @Anonymous
Try this for your percentage measure. I'm a bit confused as to where the column 'Project' belongs in your data model. I'll assume it's in table 'Cost' given the code of your [Dynamic Resource Measure]. Otherwise you get the general idea to approach the issue anyway.
PercMeasure = AVERAGEX ( VALUES ( 'Cost'[Project] ), DIVIDE ( [Dynamic Resource Measure], [Un-Dynamic Measure] ) )
You should provide more info when stating the problem, like showing your tables (there seem to be two?), their structure and their relationships. Anything that is relevant for the resolution of the problem. Otherwise people trying to help have to spend more time than necessary and try to guess the missing info. That might discourage some, lowering the likelihood of you getting responses.
Hi @Anonymous
Try this for your percentage measure. I'm a bit confused as to where the column 'Project' belongs in your data model. I'll assume it's in table 'Cost' given the code of your [Dynamic Resource Measure]. Otherwise you get the general idea to approach the issue anyway.
PercMeasure = AVERAGEX ( VALUES ( 'Cost'[Project] ), DIVIDE ( [Dynamic Resource Measure], [Un-Dynamic Measure] ) )
You should provide more info when stating the problem, like showing your tables (there seem to be two?), their structure and their relationships. Anything that is relevant for the resolution of the problem. Otherwise people trying to help have to spend more time than necessary and try to guess the missing info. That might discourage some, lowering the likelihood of you getting responses.
Very good solution!!! Thank very much to share!!!
Thanks AIB
This works really well, thanks!
For my understanding, as a new user of Power BI, when I hard code the measures [Dynamic Resource Measure] and [un-dynamic measure] into my new measure I end up with a different and inccorect answer, almost as if the 0% values are being included in the column average. Are you able to explain what Power BI is calculation behind the scences?
Thanks for the advice on future posting, will look to include relationships in the future.
@Anonymous
Cool.
I don't quite understand the question. Can you maybe explain it a bit more? Possibly with an example to illustrate what the issue is?
What do you mean by "when I hard code the measures...."?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |