FILTER and SUM

[Anonymous]

Hi, 

I am trying to create a measure/column with below example on PowerBI.  I need the selected filter from 'activity' and its 'duration' summed up into the result which also needs to relate and be connected to the 'Name' and 'Date' column as shown in my example. 

Please can you advise what would be the best option? I have tried different DAX formulas but getting error or wrong result everytime. 

Thank you very much. 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download PBIX file with the data and code below

The Data Model in Power BI doesn't have a duration data type so the times you have in your table for the Duration column will be stored as decimal numbers.

However you can still format these numbers as 'time/duration' as you have shown by using this measure

Total Time = 

VAR Sum_Elapsed_Time = CALCULATE(SUM('Table'[Duration]), FILTER(ALL('Table'), 'Table'[Date] = SELECTEDVALUE('Table'[Date]) && 'Table'[Name] = SELECTEDVALUE('Table'[Name])))

VAR _hrs = Sum_Elapsed_Time * 24
VAR hrs = INT(_hrs)
VAR _mins = (_hrs - hrs) * 60
VAR mins = INT((_hrs - hrs) * 60)
VAR secs = ROUND((_mins - mins)*60,0)

RETURN

FORMAT(hrs,"00") & ":" & FORMAT(mins,"00") & ":" & secs

which gives this

I've left the Duration and Result columns just so you can see how the durations are stored.  These can be removed from the visual. 

If you want you could also create another column to display the Duration values formatted in the same way that the Total Time column is.

Regards

Phil

[Anonymous]

Hi, apologies for the late reply and thank you so much for your help. 

My only question is how can I filter 'Activity' column by "Break" and "Lunch"? I will need to filter more options from Acitivity column too

Thank you very much 

[Anonymous]

I did manage to get it now so no worries. Thank you for your help 🙂