Utilization calculaton circular reference

[Anonymous]

Hello guys,

I have browsed the forum, but I believe I haven't really found what I'm searching for. The major trouble I have is with Circular referencing, since I want to calculate my resource utilization based on two other calculated columns. (See picture below)

In this table I have columns "Resource" and "Start of use" , which are static. Column "Today's date" is based on formula done in PowerQuery(DateTime.LocalNow())). Column "Capacity (seconds)" and "Worktime total for Resource" formula are done in PowerBI desktop as a calculated column, with formula:

Capacity (seconds) = -DATEDIFF('Resource'[Today's date];'Resource'[Start of use];SECOND)

Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]); ALLSELECTED('Resource'[Resource]))

To get the utilization % per resource I would normally divide "Worktime total for Resource" with "Capacity (seconds)" and multiply it with 100.

When trying to do the aforementioned process I get a circular reference error. One of the posts suggested to convert the calculated columns to measurs and take it from there, but when I try to create a measure, I could only find additional measures for that table.

Any help towards achieving "Utilization %" column or measure, that would react to filtering would be appreciated.

[TeigeGao]

Hi @Anonymous ,

Your scenario is a common circular dependencies problem, you can try to change the following dax

Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]); ALLSELECTED('Resource'[Resource]))

to:

Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]), 'Sheet1'[**] = ALLSELECTED('Resource'[Resource]))

For more information, please refer to this blog: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao 

Would you be able to look into the comment made in earlier post? For some reason I get an error, when trying to do Sheet1**

[TeigeGao]

Hi @Anonymous ,

If you could share the pbix to me, I would get more information about this problem, it will help me troubleshoot it.

Best Regards,

Teige

[Anonymous]

@TeigeGaoI will share the file with you via direct message.

[TeigeGao · Accepted solution]

Hi @Anonymous ,

Sorry for some latency, please refer to the following result:

We can change the DAX query for Worktime total for Resource like below:

Worktime total for Resource = CALCULATE(SUM('Sheet1'[Worktime]), FILTER(ALL(Sheet1),Sheet1[Resource] = EARLIER(Resource[Resource])))

Then create the following calculated column:

Utilization % = DIVIDE(Resource[Worktime total for Resource],Resource[Capacity (seconds)]) * 100

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao 

I tried to change the formula to your suggested format, but for some reason I get an error.
 

"Column '**' in table 'Sheet1' cannot be found or may not be used in this expression."

What I understood from the formula is that I would try to selected all of the data (columns) in page Sheet1 and have it equal to Resource table column Resource.
I tried to modify the DAX to have it with 1 asteryx, '*', but that didn't work as well.

Do you have any idea, what might be the issue in this case?

Thanks

Ramirenter