Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
steambucky
Helper III
Helper III

Getting averages from SPECIFIC rows

Units of a department are getting evaluated on 4 things.  Anywhere there is a 0 means that unit is not yet evaluated. I need to create averages from units that HAVE been evaluated. 

This is the data: 

DepartmentUnitStaff Efficency Staff trainingManager Efficency Manager Training 
Natural resources, Mines and EnergyMines3224
Natural resources, Mines and EnergyEnvironmental Science0000
Natural resources, Mines and EnergyFisheries0000
Natural resources, Mines and EnergyEnergy2342
Natural resources, Mines and EnergyClimate science1215

 

This is the desired output is something like this: 

 

 Staff Efficency Staff trainingManager Efficency Manager Training 
Natural resources, Mines and Energy - Average scores22.32.33.6

 

Doing the averages with but including the two units that have ZEROs in the data will create averages like this: 

 

 Staff Efficency Staff trainingManager Efficency Manager Training 
Natural resources, Mines and Energy - Average scores1.21.41.42.2

 

Whats the easiest way can get the averages of the units that HAVE been assessed? 

 

Note: If change my base data and subsitute "NOT ASSESSED" for the 0s, Excel is happy enough to only calculate averages on the numbers in the columns and gives me the right result. Power BI doesnt like the like a mix of data types ( numbers and text ) and and I get errors.  

1 ACCEPTED SOLUTION
edhans
Super User
Super User

You can use AVERAGEX and replace the zeros with blanks in the measure without changing your underlying data. Blanks are not evaluated with the AVERAGEX function.

 

 

 

Manager Training Avg = 
    AVERAGEX('DataTable',
        IF(
            'DataTable'[Manager Training] > 0,
            'DataTable'[Manager Training],
            BLANK()
            )
    )

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Nathaniel_C
Super User
Super User

Hi @steambucky ,
Please try this 

Mgr EfficiencyAverage no zeros = CALCULATE(AVERAGE(NR[Manager Efficency ]),NR[Manager Efficency ]<>0)

ave.PNG

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

Check if this can work for you

calculate(avergae(	Staff Efficency ),	Staff Efficency <>0)

avergae(filter(table,Staff Efficency <>0)Staff Efficency )

 

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 - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
edhans
Super User
Super User

You can use AVERAGEX and replace the zeros with blanks in the measure without changing your underlying data. Blanks are not evaluated with the AVERAGEX function.

 

 

 

Manager Training Avg = 
    AVERAGEX('DataTable',
        IF(
            'DataTable'[Manager Training] > 0,
            'DataTable'[Manager Training],
            BLANK()
            )
    )

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.