Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Average of measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Average of measure

07-18-2016
09:33 AM

Hi,

I calculated the measure for quarter 1 and quarter 2.

Where each value is basically the average of 13 weeks values for each group.

Now I want to calculate the average of only these values which are displayed in the visual table.

Group Name | 2016-Quarter 1 | 2016-Quarter 2 |

A | 96.99% | 87.86% |

B | 97.88% | 98.98% |

C | 98.46% | 97.57% |

D | 99.20% | 99.86% |

E | 100.00% | 100.00% |

F | 98.51% | 98.75% |

G | ||

H | 94.41% | 94.13% |

I | 89.69% | 96.48% |

G | 98.84% | 97.76% |

K | 99.69% | 99.25% |

L | 99.64% | 99.13% |

M | 96.37% | 95.08% |

N | 94.81% | 97.64% |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
03:20 PM

I think Habib's measure (or something similar) will work, but repeating the code seems like awful style...

Can you just do something like

Measure2 = AVERAGEX(SUMMARIZE(Table, Table[GroupName], "toAverage", [Measure]), [Measure])

? If you don't need the initial measure at all, then you could in-line the code, but even in that case I think splitting it out adds some clarity (it's hard enough to read as it is).

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-17-2018
12:52 AM

I want to calculate the average of a measure over my entire data(basically without any grouping) and I am not able to perform it by current solution provided. This is required as the visual I am using doesn't supports an average line.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-19-2018
09:45 AM

Just turn it into a calculated column instead of a measure.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
09:45 AM

You can use CALCULATE and apply filter for ALLSELECTED. Your formula will look like

**New Measure = CALCULATE(AVERAGE([column]),ALLSELECTED([table or column]))**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
11:12 AM

Thank you for reply.

I have measure which has been calculated already, I want the average of these mesaures.

The option you suggested is not working.

Any other suggestions guys?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
12:41 PM

Dou you have 1 measure or 2 measure?

If you have 2 you can do this:

NewMeasure=DIVIDE([Measure1]+[Measure2];2)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
01:22 PM

hi @Vvelarde,

I think I did not explained well.. you misunderstood me.

There is only one measure for different groups A, B, C, D...

My raw data is having columns

Week | Hours spent | Total available Hours |

1 | 2 | 50 |

2 | 2 | 50 |

3 | 3 | 50 |

4 | 4 | 80 |

5 | 8 | 80 |

6 | 12 | 100 |

Measure calculate average of efficiency of hours spent (1 - Hour spent/Total available Hours) for 13 weeks for each groups.

by using the following formula:

Measure = AVERAGEX(SUMMARIZE(Table, Table[week], "Sum of Hours", SUM(Table[hours spent]), "Average available", AVERAGE(Table[Available hours for the week])), 1 - DIVIDE([Sum of Hours], [Average available]))

As a result I get this:

Group Name | 2016-Quarter 1 |

A | 96.99% |

B | 97.88% |

C | 98.46% |

D | 99.20% |

E | 100.00% |

F | 98.51% |

G | |

H | 94.41% |

I | 89.69% |

G | 98.84% |

K | 99.69% |

L | 99.64% |

M | 96.37% |

N | 94.81% |

Average | 97.27% |

Now I want to calculate the average of these measure for different groups together shown in the table.

So basically my desired output is 97.27 in this example. Average of the measure for different groups.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
02:44 PM

Now if I understood correctly, you want average of all groups and that should remain same whatever selection you made?

Please add a new measure which should be copy of exisitng measure and add CALCULATE function along with filters..... something like this.

**AvgMeasure = CALCULATE(AVERAGEX(SUMMARIZE(Table, Table[week], "Sum of Hours", SUM(Table[hours spent]), "Average available", AVERAGE(Table[Available hours for the week])), 1 - DIVIDE([Sum of Hours], [Average available])),ALL(GroupName))**

change the red text to whatever your table name is for Groups. This will give you average of all groups.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2016
03:20 PM

I think Habib's measure (or something similar) will work, but repeating the code seems like awful style...

Can you just do something like

Measure2 = AVERAGEX(SUMMARIZE(Table, Table[GroupName], "toAverage", [Measure]), [Measure])

? If you don't need the initial measure at all, then you could in-line the code, but even in that case I think splitting it out adds some clarity (it's hard enough to read as it is).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-11-2021
04:28 AM

here the problem to overcame is : having already a calculated Measure HOW is possible to get the average? the DAX function average takes just column as input not measure ! so the question is not find another way to calculate my measurement BUT how make the average of it !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2019
12:20 PM

It worked!

However, I only can apply this measure for one group, right? In my case, I chose by date. But I also want to see the result by name.

Is there some way to do it by 2 groups?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-04-2020
11:14 PM

So what did you do, did you use it as calculated column?

I am in the same situation tight now.

I created a measure as

Mentor_Score = CALCULATE(([.Mentor_Prep]*0.25) + ([.Mentor_Experience] * 0.25) + ([.Mentor_NPS] * 0.5) * 1.00)

Now I want to find the average Mentor_Score per month and year, please how do I achieve this?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020
02:01 AM

Hi,

I think I am having a similar problem.

What was the best solution?

Regards,

Featured Topics

Top Solution Authors

User | Count |
---|---|

286 | |

148 | |

63 | |

57 | |

40 |

Top Kudoed Authors

User | Count |
---|---|

340 | |

215 | |

87 | |

67 | |

56 |