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 :

 

 

SEG_MES: is Months(1-12)

SEG_DIA: is Days(1-31)

SEG_HORA: is Hours(0-23)

INDICADOR 1: Is time(in minutes and secons)

 

So i need, calculate average or Median  for examples:

 

I need average in Month 1, Day 1, Hours 2, and show that ins graphics, with filters for show that:

 

I have this for the moment:

 

I want make the same for average, but when i use quick measure, avergae for category, dont work.

 

Forget to say, but when i use a whole number format, DAX expression work AVERAGE(nameColumn[namecolumn]), the problem is when i use a data time format 00:00:00 Hour:Min:Sec, it does not work.

 

Thanks very much, for the time read this.

 

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

2 Replies

  • 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