cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sixtoquiles
Helper II
Helper II

Average from Measure

HI, I need help...

 

I have 12 DAX to calculated a percent , my measure dax look like:

                   percent1 = iferror(sum(pass1)/sum(pass1)+sum(fail1)),"-")

                   percent2 = iferror(sum(pass2)/sum(pass2)+sum(fail2)),"-")

                   percent3 = iferror(sum(pass3)/sum(pass3)+sum(fail3)),"-")

                   percent4 = iferror(sum(pass4)/sum(pass4)+sum(fail4)),"-")

                   .

                   .

                   .

                   percent12 = iferror(sum(pass12)/sum(pass12)+sum(fail12)),"-")

 

I need to calculated the average of all percents from those measure.
                  PERCENT-AVG = AVERAGEIF(B2:B13,"<>""""")

 

Any suggestion... Thanks

Capture.JPG

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Easiest way i can think of is this:

Average Rate1 = DIVIDE(
		[Percent Rate1] + [Percent Rate2] + [Percent Rate3],
		if(
			[Percent Rate1] > 0,
			1,
			0
		) + if(
			[Percent Rate2] > 0,
			1,
			0
		) + if(
			[Percent Rate3] > 0,
			1,
			0
		)
)

View solution in original post

Hi there, I could suggest a modification to @Anonymous measure.

 

Average Rate1 =
Var PercentRate1 = IF([Percent Rate1]>0,1,0)
Var PercentRate2 = IF([Percent Rate2]>0,1,0)
Var PercentRate3 = IF([Percent Rate3]>0,1,0)
Var PercentRates = PercentRate1 + PercentRate2 + PercentRate3 DIVIDE( [Percent Rate1] + [Percent Rate2] + [Percent Rate3], PercentRates )

This just uses variables and puts them into a logical order, and then makes it easier to change it or update it going forward.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

19 REPLIES 19
GilbertQ
Super User
Super User

What you could also do to add onto @Anonymous

 

Is have a measure counting the Rows where you have a percentage measure called Percent

 

Count Rows = IF(ISBLANK([Percent]),BLANK,COUNTROWS('TableName'))

And then you could add this to the existing measure and replace the / 12 with / [Count Rows]

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

HI @GilbertQ and @Anonymous:

 

Thanks for answer, but, each percent is a individual measure, and when one of this percent is blank (NaN) can't included. Excel have AVERAGEIF.

 

 

Hi @sixtoquiles

 

I would suggest then using the DIVIDE function which caters for NaN





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

HI, thank @GilbertQ and @Anonymous for you answer, but may be i don't explain well.

 

My data is like that:

(measure) Percent Rate1 = sum(pass1)/(sum(pass1)+sum(fail1)) - i have many other columns with Fail1&Pass1, Fail2&Pass2, Fail3&Pass3,.....), using the same formula for each Pass&Fail.

 

I have: Percent Rate1, Percent Rate2, ......

 

I need then to calculate the Average of all this Percent Rates, Sometime i got CERO in some Percent Rate and I need to excluded those.

 

Any help will be VERY VERY Apreciate...

 

 

pass-fail.JPG

Anonymous
Not applicable

Create your measures: 

Percent Rate1 = DIVIDE(
     sum(pass1),
     (sum(pass1) + sum(fail1)
)

For each rate.

Now create a measure which is:

Average Rate1 = DIVIDE(
       [Percent Rate1] + [Percent Rate2] + [Percent Rate3],
       3
)

(This example uses 3 instead of 12, but you can modify.

HI @Anonymous, thanks!!! very happy!!Man Happy

 

Sorry but I have one question....

 

Those PassRate are related with a Date Filter...

Example:

Q1 - PR1 = 89%, PR2=99%, PR3=76%, PR4=0%, PR5=88% I need to exclude 0% from the average, divide by 4

Q2 - PR1 = 90%, PR2=65%, PR3=0%, PR4=0%, PR5=43%  I need to exclude 0's% from the average divide by 3

 

Is this possible? Have a dinamic average?

 

Anonymous
Not applicable

Easiest way i can think of is this:

Average Rate1 = DIVIDE(
		[Percent Rate1] + [Percent Rate2] + [Percent Rate3],
		if(
			[Percent Rate1] > 0,
			1,
			0
		) + if(
			[Percent Rate2] > 0,
			1,
			0
		) + if(
			[Percent Rate3] > 0,
			1,
			0
		)
)

View solution in original post

Hi there, I could suggest a modification to @Anonymous measure.

 

Average Rate1 =
Var PercentRate1 = IF([Percent Rate1]>0,1,0)
Var PercentRate2 = IF([Percent Rate2]>0,1,0)
Var PercentRate3 = IF([Percent Rate3]>0,1,0)
Var PercentRates = PercentRate1 + PercentRate2 + PercentRate3 DIVIDE( [Percent Rate1] + [Percent Rate2] + [Percent Rate3], PercentRates )

This just uses variables and puts them into a logical order, and then makes it easier to change it or update it going forward.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

HI @GilbertQ, i have a question..

 

I try to use VAR, but give me a sintax error. 

when type DIVIDE the suggest not appear and give an error...

 

Can you post a screenshot of the measure?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Sorry for the delay @GilbertQ, this is the error...

 

Average RATE =
var rate1 = IF([Count Close SUN]>0,1,0)
var rate2 = IF([Count Close MON]>0,1,0)
var rate3 = IF([Count Close TUES]>0,1,0)
var rate4 = IF([Count Close WEB]>0,1,0)
var rate5 = IF([Count Close THU]>0,1,0)
var rate6 = IF([Count Close FRI]>0,1,0)
var rate7 = IF([Count Close SAT]>0,1,0)
var Rates = rate1+rate2+rate3+rate4+rate5+rate6+rate7
DIVIDE(
RATE1+rate2+rate3+rate4+rate5+rate6+rate7,Rates
)

 

errorpwrbi.JPG

 

 

 

 

 

Hello Guys

 

Can you help me pls?

 

I have the table

 

Month AVG_Time 

 1         154

 2         126

 3         128

 4         159

avg       141.75

 

I would like to know the average of the following AVG_Time (141.75), but the problem is the quantity of months are variable, depending how many months i selected in the slicer

 

is there a way to calculate this measure?

Hi @DECOVIOTI

 

This measure below should work for you.

 

Average from Measure =
DIVIDE(sum('TableName'[Avg_Time),DISTINCTCOUNT('TableName'[Month]))

The above will just count the number of months selected and divide it by the Total in your AVG_Time column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

My [Avg_Time] is a measure and when i tried to replicate your formula, my measure is not available.

Do you know why?

 

hi @DECOVIOTI

 

Below is the updated measure

 

Average from Measure =
DIVIDE([Avg_Time],DISTINCTCOUNT('TableName'[Month]))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

For some reason, its not working properly. Its showing a value = 43.77 instead of 141.72.

is there a way to sum of the average?

Thanks @GilbertQ @Anonymous, I will try both suggest, and let you know the result.

 

Anonymous
Not applicable

Good suggestion @GilbertQ!

Anonymous
Not applicable

Does

PERCENTAVG = ([Percent1] + [Percent2] + [Percent3] +... + [Percent12]) / 12

Not work for you?

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!