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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
apmulhearn
Helper III
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.

 

apmulhearn_0-1689005886249.png




Primary Loan Officer NameYTD AppsYTDFundedConversion %
A2273314.54%
B1232016.26%
C17211.76%
D8112.50%
E841821.43%
F17423.53%
G1683017.86%
H2644015.15%
I761621.05%
J17211.76%
K52917.31%
L641421.88%
M22418.18%
N1191512.61%
O1700.00%
P701014.29%
Q1141412.28%
R100.00%
S741621.62%
T871719.54%
3 REPLIES 3
mlsx4
Memorable Member
Memorable Member

Hi @apmulhearn 

 

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))

 

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?

apmulhearn_0-1689085799402.png

 

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

 

I have tried again and this is my result:

mlsx4_0-1689143021585.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.