topic Re: Average of measure in DAX Commands and Tips

Average of measure

aktripathi2506 — Mon, 18 Jul 2016 16:33:46 GMT

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%

Re: Average of measure

Habib — Mon, 18 Jul 2016 16:45:23 GMT

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

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

Re: Average of measure

aktripathi2506 — Mon, 18 Jul 2016 18:12:10 GMT

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?

Re: Average of measure

Vvelarde — Mon, 18 Jul 2016 19:41:53 GMT

hi aktripathi2506

Dou you have 1 measure or 2 measure?

If you have 2 you can do this:

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

Re: Average of measure

aktripathi2506 — Mon, 18 Jul 2016 20:22:22 GMT

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.

Re: Average of measure

Habib — Mon, 18 Jul 2016 21:44:13 GMT

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.

Re: Average of measure

jahida — Mon, 18 Jul 2016 22:20:09 GMT

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).

Re: Average of measure

aditya118 — Wed, 17 Jan 2018 08:52:14 GMT

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.

Re: Average of measure

dyee4613 — Thu, 19 Jul 2018 16:45:47 GMT

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

Re: Average of measure

Anonymous — Wed, 23 Oct 2019 19:20:17 GMT

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?

Re: Average of measure

sp3jossy — Fri, 05 Jun 2020 06:14:34 GMT

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?

Re: Average of measure

GerlofVisser — Tue, 30 Jun 2020 09:01:59 GMT

Hi,

I think I am having a similar problem.

What was the best solution?

Regards,

Re: Average of measure

rdedonat — Mon, 11 Oct 2021 11:28:23 GMT

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 !

Re: Average of measure

Anonymous — Sat, 08 Jul 2023 10:38:23 GMT

This worked for my issue. Thanks!

Re: Average of measure

AtlasDLion — Mon, 24 Jun 2024 15:59:58 GMT

Hi there,

I'm new here but I think you can use AVERAGEX to solve your problem, I've read the documentation and it says this in there.

The AVERAGEX function can take as its argument an expression that is evaluated for each row in a table. This enables you to perform calculations and then take the average of the calculated values.

Re: Average of measure

foodd — Mon, 24 Jun 2024 16:21:29 GMT

Hello AtlasDLion , the reply Accepted as Solution in this legacy post added in 2016 used AVERAGEX in the DAX formula demonstrated by both Habib and jahida .