Incorrect Average Value

[Anonymous]

I need to find an average value. I have a measure that's working in all cases except one.

Every ria_id belongs to a Channel. I'd like to show the average tier_fee per channel.

Some tier_fee's may change throughout the year, as is the case with ria_id 1157, shown below. But note: there's no Date relationship to these tier_fees, nor should there be. The table on the left is just saying, "Show me any tier_fee ever applied to a ria_id."

I have a measure to calculate the average tier_fee per ria_id. (And, since every ria_id has a Channel, I'm able to get averages per Channel.) Here's the DAX measure:

Average BPS =

AVERAGEX(

VALUES(EntBillCalc[ria_id]),

CALCULATE(AVERAGE(EntBillCalc[tier_fee])))

This is returning the expected result for BD RIA and RIA Channels. But the "Bank" Channel is returning .21%, when I would expect .225% ((.20% + .25%) / 2).

Why might this DAX return .21%? How can I get this measure to return .225% for the Bank channel? (The only ria_id in the Bank channel is 1157, as shown in the image above.)

Thanks!

[Fowmy · Accepted solution]

@Anonymous 

I tested and your formula returns the expected average, please check your data table:

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Turns out it was weighted by date. User error 😥 Thanks