Forum Discussion
Average from Measure
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 ) )
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.
HI Anonymous, thanks!!! very happy!!:manhappy:
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?
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 ) )
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.
Thanks GilbertQ Anonymous, I will try both suggest, and let you know the result.
Good suggestion GilbertQ!
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?
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
)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 Anonymous
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.
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 Anonymous
Below is the updated measure
Average from Measure = DIVIDE([Avg_Time],DISTINCTCOUNT('TableName'[Month]))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?