Sum a Column by the Value in Another Column

[Anonymous]

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:

Column = CALCULATE(SUM('aseqdn (2)'[Diff. Time]),FILTER(ast,(RELATED(ast[Work Area])=EARLIER(RELATED(ast[Work Area])))))

But it isn't working (it's returning an error about their being a circular dependency). Can anyone spot what I;m doing wrong please?

[Iryna_K]

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.

[Anonymous]

.

[amitchandak · Accepted solution]

@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])))

[amitchandak]

@Anonymous , Not tested, But try like

Column = CALCULATE(SUM('aseqdn (2)'[Diff. Time]),allexcept(RELATEDTABLE(ast),ast[Work Area]))

[Anonymous]

@amitchandak I have also just tried this with the resulting error, 

[Anonymous]

@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.'