cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonesie92 Frequent Visitor
Frequent Visitor

Average of Percentage Measure

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?

 

ProjectDynamic Resource MeasureUn-Dynamic MeasureMeasure Test
Project 1£119,270£164,15372.66%
Project 2£1,310,369£1,880,50669.68%
Project 3£197,347£291,04267.81%
Project 4£803,032£1,373,91358.45%
Project 5£1,449,017£2,703,28653.60%
    
 £3,879,034£6,412,90060.49%
  Average64.44%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Average of Percentage Measure

Hi @Jonesie92

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.     

 

3 REPLIES 3
Highlighted
Super User
Super User

Re: Average of Percentage Measure

Hi @Jonesie92

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.     

 

Jonesie92 Frequent Visitor
Frequent Visitor

Re: Average of Percentage Measure

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. 

Super User
Super User

Re: Average of Percentage Measure

@Jonesie92

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