Forum Discussion

heejinyune's avatar
heejinyune
Frequent Visitor
3 years ago
Solved

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:

 

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 🙂

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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.

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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.