Forum Discussion
Sum a Column by the Value in Another Column
Hi,
I'd like to sum the values in a column based on the value in another column. Here is an example of my data:
I'd like a new column which sums the Diff. Time for each Work Area. In this example the column would show 2hrs 31mins for black work area, 15mins for red work area and 1hr 9mins for blue work area.
Here's the DAX I have currently, from an example I found on another thread on this board:
Anonymous , You might have create a new column in aseqdn (2)
Work Area = related(ast[Work Area]) // refer other option in video https://www.youtube.com/watch?v=czNHt7UXIe8
and create a new column like
Column = CALCULATE(SUM('aseqdn (2)'[Diff. Time]),FILTER('aseqdn (2)',[Work Area]=EARLIER([Work Area])))
6 Replies
- amitchandak
Super User
Anonymous , Not tested, But try like
Column = CALCULATE(SUM('aseqdn (2)'[Diff. Time]),allexcept(RELATEDTABLE(ast),ast[Work Area]))
amitchandak Thanks for responding. Unfortunately I'm getting this error 'The ALLEXCEPT function expects a table reference for argument '1', but a table expression was used.'
- amitchandak
Anonymous , You might have create a new column in aseqdn (2)
Work Area = related(ast[Work Area]) // refer other option in video https://www.youtube.com/watch?v=czNHt7UXIe8
and create a new column like
Column = CALCULATE(SUM('aseqdn (2)'[Diff. Time]),FILTER('aseqdn (2)',[Work Area]=EARLIER([Work Area])))
Try using the Time in State for Azure DevOps extension https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure .
It allows you to generate reports on time calculation in states/columns automatically.
It's not a Power BI solution, but you'll find it helpful.
