Forum Discussion

sixtoquiles's avatar
sixtoquiles
Helper II
9 years ago
Solved

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 = i...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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
		)
)
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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.

sixtoquiles's avatar
sixtoquiles
Helper II
9 years ago

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.

 

 

GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi sixtoquiles

 

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

  • sixtoquiles's avatar
    sixtoquiles
    Helper II
    9 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

      • sixtoquiles's avatar
        sixtoquiles
        Helper II
        9 years ago

        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?