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

[heejinyune]

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 🙂

[johnt75 · Accepted 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.