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

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.

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Does

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

    Not work for you?

     

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