Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello community!
I just wonder is it possible to find nearest of the average value in power bi?
It's hard to use DAX in my case as i have to query first for the period to get average value.
For example, my database looks like this:
First, I want to find the average model built time for the past month. Let's assume today date is 2022/11/08 and to get that
we need to do this calculation to find the average time for the past month = (543 + 898 + 700 + 100) / 4 = 560.25
And after that, i want to find the actual model built time which is similar with the average model built time (560.25) .
so it should be 543 minutes.
Is anyone knows how to return the nearest average value using Dax or KPI matrix?
I appreciated in advance! Thank you so much 🙂
Solved! Go to Solution.
Assuming that you already have a measure which returns the average build time, including any date-based logic you need, you could create a measure like
Model closest to avg =
VAR AvgBuildTime = [Avg build time]
VAR Result =
CONCATENATEX (
TOPN ( 1, 'Table', ABS ( AvgBuildTime - 'Table'[Build time] ), ASC ),
'Table'[Model ID],
", "
)
RETURN
Result
In the event of multiple models being equidistant from the average, this will return a comma separated list of all of them.
Assuming that you already have a measure which returns the average build time, including any date-based logic you need, you could create a measure like
Model closest to avg =
VAR AvgBuildTime = [Avg build time]
VAR Result =
CONCATENATEX (
TOPN ( 1, 'Table', ABS ( AvgBuildTime - 'Table'[Build time] ), ASC ),
'Table'[Model ID],
", "
)
RETURN
Result
In the event of multiple models being equidistant from the average, this will return a comma separated list of all of them.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
30 | |
26 |