Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show average per rows

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 row...
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago
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's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    d_gosbell 

     

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

     

     

    P.S: I am not the OP.

    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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