Show average per rows

[Anonymous]

Hi there,
I have this table and I would like to add column that hold Avg Dist per row based on slicer selection

And add a flag if Dist > Avg then 1 else 0
I couldn't get the average listed per rows

Thanks,

[d_gosbell · Accepted solution]

@VasTg that's a slightly different issue, the data set posted by the OP did not have any blanks.

If you are facing this issue you probably need to explicitly check for blank distances using something like the following.

eg.

 Avg Dist = 
IF( NOT( ISBLANK( 'Table'[Distance] ) )
    , CALCULATE(AVERAGE('Table'[Distance]),ALLSELECTED('Table'))
)

[Anonymous]

Hi @Anonymous , @VasTg ,

Please refer to the formulas below.

Avg Dist = DIVIDE(SUMX(ALLSELECTED(Table1),Table1[Distance]),COUNTROWS(ALLSELECTED(Table1)))

Dist > Avg = IF(SELECTEDVALUE(Table1[Distance])>[Avg Dist],1,0)

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Jay,
Thank you for your response, I address the blank issue for calculating Averages from previous comment
Also to work around the total above distance I did
Above Dist = Calculate(
    count(EmpDistance[EmployeeID]),
    filter(EmpDistance, EmpDistance[Distance] > [Average Dist]))
Which gave me the to
Thanks,
OdedDror

[amitchandak]

Try a measure like

measure =

var _ Avg = calculate(averagex(all(table),distance))

return

calculate(count(emp),distance>_avg)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[d_gosbell]

---

var _ Avg = calculate(averagex(all(table),distance))

---

The expression above will ignore the slicer selection, but if you change it to the following it should work the way you want

var _ Avg = calculate(average('Table'[Distance]), ALLSELECTED('Table'))

[VasTg]

@d_gosbell 

I tried that option. but it shows all the empID as below.. 

P.S: I am not the OP.

[d_gosbell · Accepted solution]

@VasTg that's a slightly different issue, the data set posted by the OP did not have any blanks.

If you are facing this issue you probably need to explicitly check for blank distances using something like the following.

eg.

 Avg Dist = 
IF( NOT( ISBLANK( 'Table'[Distance] ) )
    , CALCULATE(AVERAGE('Table'[Distance]),ALLSELECTED('Table'))
)

[Anonymous]

Hi there,

I could not bring your IF( NOT( ISBLANK( 'Table'[Distance] ) ) -- It looking for measure

so I did

Average Dist =
Var D = CALCULATE( sum(EmpDistance[Distance]),ALLEXCEPT(EmpDistance,EmpDistance[Job],EmpDistance[PAY_RUN]))
Var E = CALCULATE(
    COUNT(EmpDistance[EmpID]),ALLEXCEPT(EmpDistance,EmpDistance[Job],EmpDistance[PAY_RUN])) - COUNTBLANK(EmpDistance[Distance])
Return DIVIDE(D,E)
And it works

Thanks

Oded Dror