Average of Percentage Measure

[Anonymous]

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%

[AlB · Accepted 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.     

[Anonymous]

Very good solution!!! Thank very much to share!!!

[Anonymous]

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. 

[AlB]

@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...."?