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
- Re: Average from 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 from Measure

06-04-2017
07:44 AM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

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

07-19-2017
06:43 PM

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

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

07-19-2017
06:50 PM

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.

19 REPLIES 19

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

06-04-2017
05:55 PM

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]

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

06-07-2017
08:57 PM

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.

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

06-07-2017
09:25 PM

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

07-19-2017
05:41 PM

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

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

07-19-2017
05:57 PM

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.

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

07-19-2017
06:38 PM

HI @Anonymous, thanks!!! very 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?

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

07-19-2017
06:43 PM

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

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

07-19-2017
06:50 PM

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.

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

07-19-2017
07:35 PM

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

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

07-19-2017
07:50 PM

Can you post a screenshot of the measure?

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

08-15-2017
03:16 PM

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

)

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

09-18-2017
07:52 PM

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?

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

09-18-2017
07:57 PM

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.

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

09-18-2017
08:02 PM

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?

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

09-18-2017
08:03 PM

hi @DECOVIOTI

Below is the updated measure

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

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

09-18-2017
08:10 PM

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?

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

07-19-2017
06:52 PM

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

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

Anonymous

Not applicable

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

06-04-2017
05:06 PM

Does

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

Not work for you?

Announcements

Featured Topics

Top Solution Authors

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

278 | |

88 | |

83 | |

63 | |

58 |

Top Kudoed Authors

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

300 | |

103 | |

87 | |

79 | |

72 |