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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
heejinyune
Frequent Visitor

How to get nearest value with average value using kpi card.

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:

heejinyune_0-1667908185225.png

 

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 🙂

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.