March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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:
Department | Unit | Staff Efficency | Staff training | Manager Efficency | Manager Training |
Natural resources, Mines and Energy | Mines | 3 | 2 | 2 | 4 |
Natural resources, Mines and Energy | Environmental Science | 0 | 0 | 0 | 0 |
Natural resources, Mines and Energy | Fisheries | 0 | 0 | 0 | 0 |
Natural resources, Mines and Energy | Energy | 2 | 3 | 4 | 2 |
Natural resources, Mines and Energy | Climate science | 1 | 2 | 1 | 5 |
This is the desired output is something like this:
Staff Efficency | Staff training | Manager Efficency | Manager Training | |
Natural resources, Mines and Energy - Average scores | 2 | 2.3 | 2.3 | 3.6 |
Doing the averages with but including the two units that have ZEROs in the data will create averages like this:
Staff Efficency | Staff training | Manager Efficency | Manager Training | |
Natural resources, Mines and Energy - Average scores | 1.2 | 1.4 | 1.4 | 2.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.
Solved! Go to Solution.
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()
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @steambucky ,
Please try this
Mgr EfficiencyAverage no zeros = CALCULATE(AVERAGE(NR[Manager Efficency ]),NR[Manager Efficency ]<>0)
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
Proud to be a 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
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()
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |