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.

GilbertQ's avatar
GilbertQ
Super User
9 years ago

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]

 

 

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.