Forum Discussion

Sejigonza's avatar
Sejigonza
Regular Visitor
6 years ago
Solved

Averages or Median Times Values

Hi Everyone   First think i need to say is I apologize for my bad English, my mother lenguage is spanish..   I need calculate the averages or Median in differents  times, my data is :   ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    In Power BI, it’s not supported to display the time format value (hh:mm:ss) in chart Y axis. In your scenario, I would suggest you convert the time format values to numbers with interval seconds, then calculate average based on the seconds.

     

    Please try these two measures.

    One is to calculate the seconds of [INDICADOR 1]:

    Recuento de INDICADOR 1

    = MINUTE(Table47[INDICADOR 1])*60+SECOND(Table47[INDICADOR 1])

    The other is to calculate the average seconds of [INDICADOR 1]:

    Average =

    CALCULATE(AVERAGE(Table47[Recuento de INDICADOR 1]),ALLEXCEPT(Table47,Table47[SEG_HORA]),FILTER(Table47,Table47[SEG_MES]=MAX(Table47[SEG_MES])&&Table47[SEG_DIA]=MAX(Table47[SEG_DIA])))

     

    Here is my test table.

    The result shows:

    Here is my test pbix file.

    pbix 

    If you still have questions about it, please for free to let me know.

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi,

 

In Power BI, it’s not supported to display the time format value (hh:mm:ss) in chart Y axis. In your scenario, I would suggest you convert the time format values to numbers with interval seconds, then calculate average based on the seconds.

 

Please try these two measures.

One is to calculate the seconds of [INDICADOR 1]:

Recuento de INDICADOR 1

= MINUTE(Table47[INDICADOR 1])*60+SECOND(Table47[INDICADOR 1])

The other is to calculate the average seconds of [INDICADOR 1]:

Average =

CALCULATE(AVERAGE(Table47[Recuento de INDICADOR 1]),ALLEXCEPT(Table47,Table47[SEG_HORA]),FILTER(Table47,Table47[SEG_MES]=MAX(Table47[SEG_MES])&&Table47[SEG_DIA]=MAX(Table47[SEG_DIA])))

 

Here is my test table.

The result shows:

Here is my test pbix file.

pbix 

If you still have questions about it, please for free to let me know.

 

Best Regards,

Giotto Zhi

Sejigonza's avatar
Sejigonza
Regular Visitor
6 years ago

Thanks for you Support.

 

The most important thing, is change all time value in seconds 

 

 

In column "Dura_Tot_Seg, is sum hour, min, and seg.

 

After that i make the measurements:

 

M_Segundos1 = ROUND(CALCULATE(MOD(AVERAGE(BD[Dura_Tot_Seg]);60));0) for seconds
M_Minutos1 = ROUNDDOWN(CALCULATE(AVERAGE(BD[Dura_Tot_Seg]))/60;0)
M_Horas1 = ROUNDDOWN(CALCULATE(AVERAGE(BD[Dura_Tot_Seg]))/3600;0)
 
Is a little different you say, but i think is same results.
 
Thanks for you time, and answer