cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## Help with a measure which returns an average of items with 0s

Hi,

I have a table, pictured below.

The item I'm focused on is labeled A.

It is using the measure in B.

I want the value displayed in A to be 15.18 (the average of all 20 items) but instead it is returning 16.35, which is the average of the 18 items which do not equal zero percent.

Each percentage in the Conversion% column is correct; it's just the grand average at the bottom.

I've also included an export of that data.

 Primary Loan Officer Name YTD Apps YTDFunded Conversion % A 227 33 14.54% B 123 20 16.26% C 17 2 11.76% D 8 1 12.50% E 84 18 21.43% F 17 4 23.53% G 168 30 17.86% H 264 40 15.15% I 76 16 21.05% J 17 2 11.76% K 52 9 17.31% L 64 14 21.88% M 22 4 18.18% N 119 15 12.61% O 17 0 0.00% P 70 10 14.29% Q 114 14 12.28% R 1 0 0.00% S 74 16 21.62% T 87 17 19.54%
3 REPLIES 3
Super User

Take into account that if you use your formula, it will divide the SUM of grand total: that is: 265/1621.

You can achieve the correct result by using AVERAGEX

``Conversion % = AVERAGEX('Table',DIVIDE('Table'[YTDFunded],'Table'[YTD Apps],0))``

Helper III

Hi @mlsx4 . Thank you for the response. I've mimiced the measure you suggested, but now I'm getting all 100%. (The column labeled [YTD Apps] is called [YTDAppCount] in the data model and renamed in the visual.

Did I miss something that you can see?

Super User

Do you have any kind of filter that could be affecting?

I have tried again and this is my result:

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors