Forum Discussion

AaronRogers3's avatar
AaronRogers3
Helper I
8 years ago
Solved

Power BI Time Calculations

Hi PowerBI,   I have been asked to produce some nice visualizations for my end user. Here is some sample data that i have:   As you can see, my data mainly consists of working with times (t...
Need Help
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi AaronRogers3,

     

    As the picture you  shared, I think the time are all in text format. Here I made a sample for your reference.

     

    1. Enter the data and change the data type to Duration for the time columns in query editor.

     

    2. Add some measures as below. 

     

     

    ave-se = CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3))
    aver_sele_set = FORMAT(CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3)),"hh:mm:ss")
    Total time_num = SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME])
    Total_Time = FORMAT(SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME]),"hh:mm:ss")

     

    3. Then we can get a report like below.

     

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/bzyc11bqjiuktnb/Power%20BI%20Time%20Calculations2.pbix?dl=0

     

    Regards,
    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi AaronRogers3,

 

As the picture you  shared, I think the time are all in text format. Here I made a sample for your reference.

 

1. Enter the data and change the data type to Duration for the time columns in query editor.

 

2. Add some measures as below. 

 

 

ave-se = CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3))
aver_sele_set = FORMAT(CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3)),"hh:mm:ss")
Total time_num = SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME])
Total_Time = FORMAT(SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME]),"hh:mm:ss")

 

3. Then we can get a report like below.

 

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/bzyc11bqjiuktnb/Power%20BI%20Time%20Calculations2.pbix?dl=0

 

Regards,
Frank

AaronRogers3's avatar
AaronRogers3
Helper I
8 years ago

Hi v-frfei-msft 

Frank,

 

Thanks! I have made calculated columns that have the average times for each of my stages.

 

I was wondering, is it impossible to make these new columns that i have created reactive?

 

For example, if i click the NOACK option for machine name then they change to the correct data?

 

Thanks